How to do Simple Forecast of an Accumulated Loss Amount (Charts & Value)

[hykal1993]

Hi,

Can anyone help me on how to provide simple forecast for the loss amount below? If you notice in Aug 2017, the accumulative amount is similar until the rest of the year since there is no data. I would like to replace the data with accumulation of the forecast value. I would also like the value to be reflected in a table. If the answer is in script, please provide the full script since I am still new with the language.

Also, appreciate if you can explain the logic behind the forecast as well. PFA the file for your reference. Thanks.

[tresesco]

Have a look here: Forecasting in Qlikview

[hykal1993]

Hi Tresesco,

When I use the method, it will create a new line for forecast, not extension of 2017 data. Btw, how to attach the files? I cannoy attach qlikview file I created.

[tresesco]

If you reply from the discussion thread (not from inbox), you should get 'Use advanced editor' at the right-top after clicking the reply option. Then, there at the right-bottom we get to attach your file.

Try to create numeric/dual month values using

Month(Date) as Month

Or,

Month(Date#(Year&Month, 'YYYYMMM')) as Month

Or,

Num(Month(Date#(Year&Month, 'YYYYMMM'))) as MonthNum

Then try/share the qvw.

[hykal1993]

My date has been separated. I have three columns in my data, Month, Year and Grand Total. PFA

[tresesco]

PFA.

As suggested above, create a numeric month field and then use expression something like:

if(sum({<Year={2017}>}[Grand total]),RangeSum(Above(sum({<Year={2017}>}[Grand total]),0,RowNo(TOTAL)))

,linest_m(total aggr(if(sum({<Year={2017}>}[Grand total]),sum({<Year={2017}>}[Grand total])),MonthNum),MonthNum)*MonthNum

+linest_b(total aggr(if(sum({<Year={2017}>}[Grand total]),sum({<Year={2017}>}[Grand total])),MonthNum),MonthNum))

[sunny_talwar]

Or may be this

if(sum({<Year={2017}>}[Grand total]),RangeSum(Above(sum({<Year={2017}>}[Grand total]),0,RowNo(TOTAL)))

,Sum(TOTAL {<Year={2017}>}[Grand total]) + linest_m(total aggr(if(sum({<Year={2017}>}[Grand total]),sum({<Year={2017}>}[Grand total])),MonthNum),MonthNum)*MonthNum

+linest_b(total aggr(if(sum({<Year={2017}>}[Grand total]),sum({<Year={2017}>}[Grand total])),MonthNum),MonthNum))

[sunny_talwar · Accepted solution]

Actually this might be the best

RangeSum(Above(if(sum({<Year={2017}>}[Grand total]),sum({<Year={2017}>}[Grand total])

,linest_m(total aggr(if(sum({<Year={2017}>}[Grand total]),sum({<Year={2017}>}[Grand total])),MonthNum),MonthNum)*MonthNum

+linest_b(total aggr(if(sum({<Year={2017}>}[Grand total]),sum({<Year={2017}>}[Grand total])),MonthNum),MonthNum)), 0, RowNo()))

[hykal1993]

Thank you so much, Sunny! Btw, how do I customize the colour of the forecast value? Do I need a code for that? Also, any chance I can show month as text in the x-axis instead of number?

[sunny_talwar]

I used this to convert MonthNum to a Month, but you can do this in the script also where you might have already created a Month field and use that as your dimension

=Month(Date#(MonthNum, 'M'))

For color, I used this as a background color expression

if(sum({<Year={2017}>}[Grand total]),LightCyan(), LightMagenta())