Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

New Contributor III

Calculating Weeks of Sales

To the retail gurus, I need a little bit of help in a certain formula.

I've been trying to figure out how to do a Weeks of Sales calculation over a straight table chart.

To illustrate what I'm trying to accomplish, please see the table below.

Fiscal PeriodBOPSLS RetailWeeks of Sales
P014,600,000.001,211,393.00                3.44
P024,429,066.001,072,099.00                3.29
P034,584,333.001,766,039.90                6.04
P044,119,848.001,238,298.00                8.87
P053,922,274.001,198,110.00              15.20
P064,341,743.72350,615.56              74.30

Weeks of Sales is calculated by looping through each SLS Retail row and getting the number of periods/weeks BOP could cover it given the actual sales per period. For example, the P01 BOP of 4,600,000 could be spread over roughly 3.44 periods (total SLS Retail from P01 to P03 = 4,049, 531).

I've been exploring combining rangesum and chart inter-record functions but I can't figure out how to make it conditional such that it compares BOP with a running sum of SLS Retail.

Any hint would be of great help.


Community Browser