topic Re: Partial Sum problem in QlikView

Partial Sum problem

Prav — Mon, 14 Oct 2019 11:01:26 GMT

Hi,

I have a pivot table like below. Bold ones are partial sums.

I am struggling with expression 'H'. For 'H', I need to multiply the TOTAL of 'G' which is 173,157.33 with expression 'B' which is 7.94 and then divided by 100. The result for first column should be something like (173,157.33 * 7.94)/100 =13,748.69. But it is showing like 19,589.26 which is incorrect.

Expression 'G' = E - F.

Can anyone help me on this?

Many thanks.

Re: Partial Sum problem

Prav — Mon, 14 Oct 2019 13:06:46 GMT

I have also tried advanced aggregation: sum(aggr(expression,Dimension)) but no use. Can anyone help me on this please?

Re: Partial Sum problem

Brett_Bleess — Mon, 21 Oct 2019 19:19:16 GMT

Have a look at the following Help link:

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/NestedAggregations/sum-of-rows-pivot-tables.htm

The only other thing of which I can think is your needing to use RowNo() or RecNo() in the load script to be sure every row in your data is unique to ensure you get what you want in your calculations.

Only other link I have is the following that may be helpful related to AGGR:

https://community.qlik.com/t5/Qlik-Design-Blog/AGGR/ba-p/1475149

If you want to search further on the Design Blog area, use the following link:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Hopefully one of these will prove to be helpful.

Regards,
Brett

Re: Partial Sum problem

sunny_talwar — Mon, 21 Oct 2019 20:47:14 GMT

What expressions are you using for all the different rows? Can you share a sample to help you better here?

Re: Partial Sum problem

Prav — Thu, 21 Nov 2019 16:46:27 GMT

Hi,

Sorry for late reply. I solved this issue by creating individual variables and added them all.

Thanks.