Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cscliffe10x
New Contributor II

Cumulative Forecast

Hello,

I am trying to create a cumulative sum forecast trend line but I am struggling with the syntax of linest_m and linest_b

Measure:

RangeSum(Above(TOTAL Sum({<[Data Source] = {'Bookings'},[Date.autoCalendar.InYTD]={1}>}[Instrument Quantity]),0,RowNo(TOTAL)))

Dimension:

[Date.autoCalendar.Date]

The part, in particular, that is proving to be tricky is making the linest_m portion of the equation be a cumulative sum.  I am hoping that I can handle this outside of the script.  Thank you in advance for your help.

Best regards,

Conor

4 Replies
YoussefBelloum
Esteemed Contributor

Re: Cumulative Forecast

Hi,

why did you put TOTAL after the sum and inside Rowno() ?

Try this:

RangeSum(Above(Sum({<[Data Source] = {'Bookings'},[Date.autoCalendar.InYTD]={1}>}[Instrument Quantity]),0,RowNo() ))

cscliffe10x
New Contributor II

Re: Cumulative Forecast

So the 'TOTAL' is for the cumulative piece.

Here is my full formula, which still does not capture the cumulative component:

linest_m(total aggr(if(Sum({<[Data Source] = {'Bookings'},[Date.autoCalendar.InYTD]={1}>}[Instrument Quantity]),

Sum({<[Data Source] = {'Bookings'},[Date.autoCalendar.InYTD]={1}>}[Instrument Quantity])),


[Date.autoCalendar.Date]),[Date.autoCalendar.Date])*


only({1}[Date.autoCalendar.Date])+linest_b(total aggr(if(Sum({<[Data Source] = {'Bookings'},[Date.autoCalendar.InYTD]={1}>}[Instrument Quantity]),

Sum({<[Data Source] = {'Bookings'},[Date.autoCalendar.InYTD]={1}>}[Instrument Quantity])),[Date.autoCalendar.Date]),[Date.autoCalendar.Date]) 

YoussefBelloum
Esteemed Contributor

Re: Cumulative Forecast

would you be able to attach some data ? some rows from your table ? some sample rows that represent your table structure ?

cscliffe10x
New Contributor II

Re: Cumulative Forecast

Certainly - please see attached.

Community Browser