Pivot Table - calculated field based on a grouped field
Stephv74
-
ALS35 Posted messages 1034 Registration date Status Membre Last intervention -
ALS35 Posted messages 1034 Registration date Status Membre Last intervention -
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.
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 réponses
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
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
Give an example of your file by going through cjoint.com or mon-partage.fr.