View original (French)

Pivot Table - calculated field based on a grouped field

Stephv74 -  
Hello,

I have a Pivot Table with a date field. I've grouped the date field to visualize the data by month, quarter, or year. Like this:

+2019
- 2020
-Quarter 1
January
February
March
-Quarter 2
April
May
June
+Quarter 3
+Quarter 4

I want to create a calculated field that uses the number of days in the time period of the displayed row. For example, if I have a value V in my table, the formula for my calculated field will look like V / 'Number of days in the time period'.

The result will look like this:

My calculated field
+2019 =V/365
- 2020 =V/366
-Quarter 1 =V/91
January =V/31
February =V/29
March =V/31
-Quarter 2 =V/91
April =V/30
May =V/31
June =V/30
+Quarter 3 =V/92
+Quarter 4 =V/92

The first problem I encounter is being able to use the date field (aggregated or not) in the formula for my calculated field. Is it possible?

Second problem: is it possible in a calculated field to identify the level of aggregation (Month or Quarter or Year) to adjust the formula accordingly?

Thank you in advance for your ideas.

2 answers

  1. ALS35
    Hello,

    Isn't it the average of your values V that you want to do?
    If so, then you place your field V in the values area of the pivot table, a second time if it's already there, and in Value Field Settings you replace the summary function Sum with Average.
    And if you want the number of days, you place your date field in the values area and replace the summary function with Count if necessary.

    Best regards
    0
    1. stephv74
      Thank you ALS35, but it's not the average I'm trying to obtain. It's the ratio of V by the number of calendar days per month or quarter or year (depending on the row in the TCD).
      0
    2. ALS35
      @stephv74And what is V in your comic and your TCD?
      Give an example of your file by going through cjoint.com or mon-partage.fr.
      0
    3. stephv74
      @ALS35V is any numeric field in my database.
      I will create a file with just a few sample data to share tomorrow.
      0
    5. ALS35
      @stephv74Hello,

      I don't think we can use calculated fields for your needs; the possibilities in terms of formulas for these fields are quite limited, and you have too many different formulas. A pivot table requires a certain level of homogeneity.

      However, by adding columns to your base table, like the number of days in the year, the quarter, and the month, and then calculating the corresponding percentages, we should be able to summarize this with pivot tables.
      It should also be possible to create a table that resembles what you want but is more static using the SUMPRODUCT (or SUMIFS) function while manually applying the appropriate percentage formulas on each row.

      Let me know if this makes sense to you; otherwise, I can suggest something along those lines tomorrow.

      Best regards.
      0