Hi Gurus,
I'd like to ask your advise on an issue i am encountering.
I have a pivot report that summarizes the actual/forecasted sales per week, summed by period, and then by quarter, then by year.
The report is having issues when calculating for the period total affecting quarter and year grand total.
Here is my setup:
It has a column 'Forecast/Actual' which formula is on a case statement:
1. When the week of the row is < current week
It gets the actual sales from a table called 'Fact - Sales' using FILTER()
2. But when week is > current week
It gets the forcasted sales from a table called 'Forecast - Sales' using FILTER()
Example: Current week is 23
| Quarter | Period | Week | Forecast/Actual | Variance |
| 2 | 6 | 21 | 2000 | |
| 22 | 2500 | |
| 23 | 3000 | |
| 24 | 2500 | |
| Total: | 5500 | |
The weekly totals are correct however the period total is incorrect.
I found out that it was summing up the actual sales from 'Fact - Sales' and not displayed table, which is incorrect.
Note that this inconsistency only happens when the current date falls on the period.
These can be fixed by using Aggregation Rule > SUM to the column sales however It does not reflect to the other columns called 'Variance' which' formula uses 'Fact - Sales'.
The column 'Variance' is still using the incorrect SUM instead of the fixed one('Aggregation Rule > SUM).
My questions are:
1. How can i set up the column variance to use the value displayed from report, 'Aggregation Rule' > SUM, and not the incorrect one.
2. Are there any ways I can get the correct output?
Thanks,
Ivan