Calculating percentage based on Sum of Rows instead of Expression total

[MK05]

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.

[BrunPierre]

Have you already tried the simple sum of rows in a straight table?

[GeigerS]

I appreciate the information and advice you have shared. I will try to figure it out for more.

---

@MK05 Syracuse Weather wrote:

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.

---

[MK05]

Hello All, thanks for your replies. But I have solved with the sum aggr function. Thank you for considering to look into this. 

[MK05]

Thank you - just wanted to leave a note - I have solved this. Thank you for considering to look into this. Appreciate.

[MK05]

Yes I did that, however my savings percent was incorrect, but I was able to solve this with sum aggr function.