Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
stark___
Contributor III
Contributor III

Incorrect multiplication in the pivot table

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:

Таблица 1.jpg

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:

Таблица 2.jpg

What am I doing wrong?

Best regards, Stark

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

5 Replies
sunny_talwar

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

View solution in original post

stabben23
Master
Master

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

stark___
Contributor III
Contributor III
Author

Thank you,Sunny.

Your formula works perfect.

Best regards,Stark

stark___
Contributor III
Contributor III
Author

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

stabben23
Master
Master

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