Pivot Table - calculated field based on a grouped field
Stephv74
-
ALS35 Posted messages 1034 Registration date Status Member Last intervention -
ALS35 Posted messages 1034 Registration date Status Member 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.
Related links:
- [Access] calculated field with condition
- Pivot table, IF function in a calculation
- how to integrate an IF function in a calculated field
- Error "The pivot table field name is not valid"
- ... darned answer ... we cannot summarize this field with Sum because ....
- [TCD] Percentage calculation between two columns
2 answers
-
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