topic Monthly Weighted distribution for a product in QlikView

Monthly Weighted distribution for a product

Anonymous — Sun, 03 Jan 2016 09:36:32 GMT

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.

Re: Monthly Weighted distribution for a product

HirisH_V7 — Sun, 03 Jan 2016 14:37:40 GMT

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

Re: Monthly Weighted distribution for a product

Oleg_Troyansky — Sun, 03 Jan 2016 18:24:35 GMT

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!