topic Re: Previous 13 weeks Sales in QlikView

Previous 13 weeks Sales

Thu, 07 Aug 2014 23:21:39 GMT

Hi,

I am trying to calculate a specific metric for business which is called Weeks of Stock. The formula is Latest week inventory/avg of last 13 weeks sales. I have attached an example in excel. In excel its pretty simple to do, but how can I write an expression Qlikview which will give me sum of previous 13 weeks sales out. Please refer the excel to see the required calculation.

Any help would be appreciated.

Re: Previous 13 weeks Sales

rwunderlich — Thu, 07 Aug 2014 23:33:45 GMT

Excellent paper on the subject here:

Calculating rolling n-period totals, averages or other aggregations

-Rob

Re: Previous 13 weeks Sales

Fri, 08 Aug 2014 16:07:38 GMT

Thanks for thread. I applied the set expression and its working when the week is arranged vertically. However my specific requirement to arrange the week in a horizontal way.

The expression I used

sum([Inventory Net USD Price])/(rangesum(above(total sum([Sellout Net USD Price]),0,13))/13)

Requirement:

Hope you can help me modify the expression to accommodate weeks in above manner.

Simky

Re: Previous 13 weeks Sales

rwunderlich — Fri, 08 Aug 2014 16:13:38 GMT

Take a look at before() instead of above().

-Rob

Re: Previous 13 weeks Sales

Fri, 08 Aug 2014 16:16:31 GMT

I tried that already, I think rowno or columnno need to be applied.