Qlik - Forecasting Based on Previous Year

[psenthilbtech]

Hi

I have a scenario, where i have to forecast the data based on the last Year Month Data. Below is the sample data

Qn : With the given data below - i need to forecast the data for Sep-20 till Dec - 2020 (Based on the Months Previous Year Data)

Ex:  For Sept 2020 - I should get the value of Sept 2019, as i dont have the value for Sept 2018

For Oct  2020 - I should get the value of Oct 2019, as i dont have the value for OCt 2018

For Nov 2020 - I should get the Average value of Nov2019 and Nov 2018 and goes on

Month	PB
Nov-18	9041
Dec-18	9747
Jan-19	9243
Feb-19	7839
Mar-19	13000950
Apr-19	10621127
May-19	11752534
Jun-19	11144306
Jul-19	9192501
Aug-19	10075416
Sep-19	7092894
Oct-19	5640611
Nov-19	4613950
Dec-19	4557715
Jan-20	4384742
Feb-20	4777633
Mar-20	7893871
Apr-20	4339084
May-20	4649449
Jun-20	5785399
Jul-20	4760955
Aug-20	4747258
Sep-20
Oct-20
Nov-20
Dec-20

@sunny_talwar 

Regards,

SK

[chrismarlow]

Hi,

If you just need to go back 2 years you could try using above function, so something like following might work;

(alt(above(Sum(Amount),12,0),0)+alt(above(Sum(Amount),24,0),0))/(alt(above(Count(Amount),12,0),0)+alt(above(Count(Amount),24,0),0))

Note you may need to do something different with the denominator (to decide if you are dividing by 1 or 2), depending on how your data is structured.

If you need to go back over time further as this rolls forward, suggest you look at this;

The As-Of Table - Qlik Community - 1466130

Cheers,

Chris.

[AT_7]

Can you post solution or script here instead of QVW . That will be really helpful