Previous 13 weeks Sales

[#1]

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.

[rwunderlich]

Excellent paper on the subject here:

Calculating rolling n-period totals, averages or other aggregations

-Rob

[#3]

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

[rwunderlich]

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

-Rob

[#5]

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