Monthly Weighted distribution for a product

[Anonymous]

Hi All,

I want to calculate Monthly weighted distribution of a product.

I have Monthly data for various stores. each store is selling multiple products. now if a store is selling say product U then my weighted distribution should be calculated as

WD = (Total sale of all stores which sold a product U in given month) / Total sales in the given month.

I have attached a sample data set and desired output.

Please help me out to get this calculation.

Thanks

Aashish.

[HirisH_V7]

Hi,

Check this,

Temp:
LOAD  Mid(Period,7,8)&'/'&Mid(Mid(Period,5,6),1,2)  &'/'&Mid(Period,1,4)  as Date,
      Store,
      Period,
     Product,
     Amount
FROM
[WD.xlsx]
(ooxml, embedded labels, table is Sheet1);


Table:
Load *,
Month (Date) as Month
resident Temp;
Drop table Temp;

Output:

HTH,

PFA,

Regards,

Hirish

[Oleg_Troyansky]

I think you simply need a chart (Straight Table or a Pivot Table) with Product and Period as Chart Dimension.

The expression should be simply this:

sum(Sales) / sum( TOTAL <Period> Sales)

With Product and Period used as Chart dimensions, sum(Sales) returns the total sales per Product per Period (across all stores), and the denominator contains the total Sales by Period (across all stores and all products).

You can learn in detail how QlikView aggregation works, along with many other QlikView techniques, from my new book QlikView Your Business.

cheers,

Oleg Troyansky

Take your Qlik Skills to the next level at the Masters Summit for Qlik - now with new and redesigned materials!