Calculate Average Price of a Product Selled by Kilograms

[cristian_av]

Hi

I have a table with about 20 millions of records (And growing)

This table has sales of fruit, and as you can imagine, the fruit is selled by KG not by unit.

I need to get the average price by each kind of fruit (or product) by product/customer/market/others...

The trouble I'm having is, ¿How should I calculate the average price, considering that I need to know the total of the KG for each product/customer/market/others?

I've uploaded an excel showing example data and calculation steps that I think are the correct, but How do I implement this on QlikView? Should I walk throught the whole table for each product, then customer, then market and so on?

Thanks!!

[antoniotiman · Accepted solution]

Hi,

See Attachment.

Regards,

Antonio

[cristian_av]

Thanks. But I wonder if considering the amount of rows (20.000.000 by years, and I need to compare between years), would it be a good idea to use aggr, or should I process the data in the load script?

Thanks!

[antoniotiman]

I think Aggr() is better, however You can try both ways.

Depending on number of distinct values of any dimension.