# Qlik Sense App Development

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

Tags (2)
4 Replies
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() ))

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

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 ?

New Contributor II