Hello,
How do I solve the Savings % problem.
I have a set analysis, would like to calculate savings percentage based on the sum of rows from previously defined columns such as Savings and Spend in the set analysis straight table. However QlikView takes the expression totals, my savings percentage is incorrect for expression totals and is correct if manually calculate it by sum of the totals of the savings column and spend column. Please see attached sample data set and the definitions below for the calculations.
1. Spend by User Input: set analysis to calculate total spend between dates chosen by the user
2. Quantity by User Input: set analysis to calculate total qty between dates chosen by the user
3. Baseline: Spend by user input/quantity by user input at product row level
4. Spend Current Year: Spend for the product in current year
5. Quantity current year: Quantity for the product in current year
6. Weighted Average Unit Price Current Year: Spend - Current Year/Quantity - Current Year calculated at product row level
7. Savings: (Baseline - Weighted Average Unit Price)*Quantity - Current year, calculated at product row level
8. Savings percent - Sum of Savings/(Sum of savings+ Spend Current Year). Per this calculation the savings percent for the sample data set is -11.12/(-11.12+5175.40)=-0.22%. However due to expression totals the calculation shown by QlikView is not correct.
I am not able to do this in the backend script as I have baseline calculated based on user driven date ranges.
