topic Re: Cumulation in straight table in QlikView

Cumulation in straight table

NickBentley — Wed, 10 Jan 2024 05:28:14 GMT

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	-	-

Re: Cumulation in straight table

Ahidhar — Wed, 10 Jan 2024 10:15:15 GMT

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')

Re: Cumulation in straight table

Parthiban — Wed, 10 Jan 2024 10:22:40 GMT

Hi,

try this one,

=aggr(rangesum(below(Forecast,2),Below(Forecast,1),Sum(Forecast)),Month)

=aggr(if(not isnull(below(sum(Forecast),2)),rangesum(below(sum(Forecast),0,3)),null()),Month)

Re: Cumulation in straight table

NickBentley — Wed, 10 Jan 2024 22:41:11 GMT

Bottom expression has given the result I was after, thanks for your help!