Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
|
Regards,
SK
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.
Can you post solution or script here instead of QVW . That will be really helpful