Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am looking at building a table (possibly a bar chart) for forecasting requirements where the stock required is the forecast for the current month plus the forecast for the next two months.
Example - stock required for January will 10,091 which is the sum of Jan (3,667) Feb (4,000) and March (2,424).
Not sure if the best approach is with a calculated dimension or through set analysis - not having much success with either. Table below has been done in Excel but I am aiming to recreate this in QlikView.
Thanks in advance for your help 🙂
Month | Forecast | Stock Required | Comments |
Jan-24 | 3,667 | 10,091 | Sum of this month plus next 2 months |
Feb-24 | 4,000 | 7,745 | Sum of this month plus next 2 months |
Mar-24 | 2,424 | 4,937 | Sum of this month plus next 2 months |
Apr-24 | 1,321 | 3,387 | Sum of this month plus next 2 months |
May-24 | 1,192 | 3,096 | Sum of this month plus next 2 months |
Jun-24 | 874 | 4,519 | Sum of this month plus next 2 months |
Jul-24 | 1,030 | 6,635 | Sum of this month plus next 2 months |
Aug-24 | 2,615 | 11,622 | Sum of this month plus next 2 months |
Sep-24 | 2,990 | 13,372 | Sum of this month plus next 2 months |
Oct-24 | 6,017 | 15,122 | Sum of this month plus next 2 months |
Nov-24 | 4,365 | - | - |
Dec-24 | 4,740 | - | - |
Hi,
try this one,
=aggr(rangesum(below(Forecast,2),Below(Forecast,1),Sum(Forecast)),Month)
or
=aggr(if(not isnull(below(sum(Forecast),2)),rangesum(below(sum(Forecast),0,3)),null()),Month)
try this in front end
Stock Required - if(not isnull(below(sum(Forecast),2)),rangesum(below(sum(Forecast),0,3)),null())
Comments - if(not isnull(below(sum(Forecast),2)),'Sum of this month plus next 2 months')
Hi,
try this one,
=aggr(rangesum(below(Forecast,2),Below(Forecast,1),Sum(Forecast)),Month)
or
=aggr(if(not isnull(below(sum(Forecast),2)),rangesum(below(sum(Forecast),0,3)),null()),Month)
Bottom expression has given the result I was after, thanks for your help!