topic Incorrect multiplication in the pivot table in QlikView

Incorrect multiplication in the pivot table

stark___ — Thu, 14 Apr 2016 11:53:58 GMT

Colleagues, good afternoon.

I have a small problem that I cannot understand and need your advice.

We have a report of distributor sales of our products through their clients. The report shows only the quantity, and to evaluate the sale, we need to multiply the quantity and our prices. Actual prices are pulled from PriceBook, that created during the loading script. The link between the report and the PriceBook through key: product name + date.

I am trying to create a pivot table that displayed the sales in monetary terms and there is a problem:

If I use sum (Quantity * Price), it turns out some nonsense (the result in the last column). Prices and the nomenclature are linked correctly, multiplication of columns Quantity and Price gives the correct result, the formula sum (Quantity) * Price, also gives the correct result, but it does not show Total when minimized nomenclature.

I tried to make PriceBook based QVD, but the same result. The most interesting, if PriceBook uploaded to Excel file and load back, sum (Quantity * Price), gives the correct result:

What am I doing wrong?

Best regards, Stark

Re: Incorrect multiplication in the pivot table

sunny_talwar — Thu, 14 Apr 2016 12:53:16 GMT

Try this:

Sum(Aggr(Sum(Quantity) * Price, AllYourChartDimensionsHere))

Make sure to replace AllYourChartDimensionsHere with all the dimensions you are using in the pivot table separated by comma

Re: Incorrect multiplication in the pivot table

stabben23 — Thu, 14 Apr 2016 12:59:44 GMT

Price is not aggregated on the same Level as Quantity, therefore you need to use aggr Down to lowest Level in Your pivot. All value needs an aggregatiojn if you want sum on top in pivot

Re: Incorrect multiplication in the pivot table

stark___ — Thu, 14 Apr 2016 13:33:02 GMT

Thank you,Sunny.

Your formula works perfect.

Best regards,Stark

Re: Incorrect multiplication in the pivot table

stark___ — Thu, 14 Apr 2016 13:39:59 GMT

Thank you, Staffan for your reply, but I have other question: why sum(Quantity * Price) works correct without aggregation, if I load PriceBook from Excel file?

Best regards, Stark

Re: Incorrect multiplication in the pivot table

stabben23 — Thu, 14 Apr 2016 13:55:42 GMT

Spooky, but maybe Excel do some aggregation for you, never mind if you have correct solution.