Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

How to calculate Weeks of Coverage for inventory (current SOH / next 3 months of sales)

Hello,

I need to calculate the weeks of coverage (WoC) for each month in my table. The formula is : (inventory of the month) / ((sum of next 3 months)/13).

I have a pivot table like this:

 FebMarAprMayJunJul
Sales100200300200300100
Inventory10001500900200025001500
Weeks of Coverage      

 

Feb WoC = (1000 / ((200+300+200)/13) = 18.6

Mar WoC = (1500 / ((300+200+300)/13) = 24.4

etc.

in my table, i use dimensions Month (text based) (Jan, Feb, Mar etc.). (I have another dimension for month in numeric: Month.nb (1,2,3,etc.) which is not in the dimensions of my table, it's a helper)

What should be the correct set expression to get what i need ?

For the moment, as a practice, I tried to simply get the sum of sales of the next 3 months, but it does not work. For each month (jan, feb, mar), I get the sales of that same month with the below expression:

Sum({$<[Year]={$(=Only([Year]))}, [Month.nb]={1,2,3}>}[WF SALES C US$])

(with above, i expected to get for each Month (jan to dec) the sum of sales of Month 1+2+3. But I get sales of month 1 for Jan, sales of month 2 for Feb, sales of Month 3 for March and no sales for the other months)

The selection is only the year and the region. There is no other selection related to the period or time.

Thank you

 

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Hi,

If the mentioned table is pivot table you can use the below expression to get the coverage:

SUM(Inventory)/((AFTER(SUM(Sales))+AFTER(SUM(Sales),2)+AFTER(SUM(Sales),3))/13)

View solution in original post

2 Replies
Highlighted
Partner
Partner

Hi,

If the mentioned table is pivot table you can use the below expression to get the coverage:

SUM(Inventory)/((AFTER(SUM(Sales))+AFTER(SUM(Sales),2)+AFTER(SUM(Sales),3))/13)

View solution in original post

Highlighted
Contributor
Contributor

Thank you ! it does work in pivot table so I accept the solution.

I'm trying to do the exact same thing, but this time displaying as a bar chart, but it seems not working in this case.

Do  you have another trick up your sleeves ? 🙂

Thank you

 

edit: just realized the after() does not work for the last 3 months in my pivot table (Oct, Nov Dec) as there are not other value after them. This is ok if I checked current year. But I checked for a past year, I would like to see the correct WOC (but I don't want to display 2 years of data in the pivot table...