Hi Qlik Experts,
I have the below data in the table month wise, and the data is complete with actuals reported until Jan 01 2023 (Dates are in the format of DD/MM/YYYY with only month starting dates). I want to calculate the forecast of Future months Feb 01 2023 to Dec 01 2023 based on the data of last month which is Jan 01 2023.
For Eg, for the month of February 01 2023 , the field Total hours EOM should be calculated based of the figures of Jan 01 2023 (Total Hours EOM for January(6396) + New hours added for January 2023(78) - Completed hours for January 2023 (255)). In the same way for the Month of March 01 2023, we need to calculate the columns based on above acquired Feb figures. I have posted the desired output table below as well.
Input:
|
DATE
|
Completed hours
|
Total hours EOM
|
New hours added
|
Total Hours at Month starting
|
|
01/03/2022
|
490
|
6023.5
|
2341
|
10093
|
|
01/04/2022
|
494
|
5588.5
|
118
|
6023.5
|
|
01/05/2022
|
578.8
|
5836.5
|
359
|
5588.5
|
|
01/06/2022
|
545.7
|
5828.5
|
160
|
5836.5
|
|
01/07/2022
|
519.85
|
5808.5
|
229
|
5828.5
|
|
01/08/2022
|
382.35
|
6760.5
|
1124
|
5808.5
|
|
01/09/2022
|
308.05
|
5967.5
|
469
|
6760.5
|
|
01/10/2022
|
255.7
|
6431
|
678.5
|
5967.5
|
|
01/11/2022
|
387.75
|
6652
|
350
|
6431
|
|
01/12/2022
|
253.5
|
6652
|
21
|
6652
|
|
01/01/2023
|
255
|
6396
|
78
|
6652
|
|
01/02/2023
|
495.4529743
|
|
538.86
|
6396
|
|
01/03/2023
|
497.4272331
|
|
538.86
|
|
|
01/04/2023
|
342.0636413
|
|
538.86
|
|
|
01/05/2023
|
420.4345692
|
|
538.86
|
|
|
01/06/2023
|
360.0698326
|
|
538.86
|
|
|
01/07/2023
|
376.2096816
|
|
538.86
|
|
|
01/08/2023
|
356.3830067
|
|
538.86
|
|
|
01/09/2023
|
484.2190118
|
|
538.86
|
|
|
01/10/2023
|
525.0736772
|
|
538.86
|
|
|
01/11/2023
|
541.5184192
|
|
538.86
|
|
|
01/12/2023
|
415.6753953
|
|
538.86
|
|
Output:
|
DATE
|
Completed hours
|
Total hours EOM
|
New hours added
|
Total Hours at Month starting
|
|
01/03/2022
|
490
|
6023.5
|
2341
|
10093
|
|
01/04/2022
|
494
|
5588.5
|
118
|
6023.5
|
|
01/05/2022
|
578.8
|
5836.5
|
359
|
5588.5
|
|
01/06/2022
|
545.7
|
5828.5
|
160
|
5836.5
|
|
01/07/2022
|
519.85
|
5808.5
|
229
|
5828.5
|
|
01/08/2022
|
382.35
|
6760.5
|
1124
|
5808.5
|
|
01/09/2022
|
308.05
|
5967.5
|
469
|
6760.5
|
|
01/10/2022
|
255.7
|
6431
|
678.5
|
5967.5
|
|
01/11/2022
|
387.75
|
6652
|
350
|
6431
|
|
01/12/2022
|
253.5
|
6652
|
21
|
6652
|
|
01/01/2023
|
255
|
6396
|
78
|
6652
|
|
01/02/2023
|
495.4529743
|
6219
|
538.86
|
6396
|
|
01/03/2023
|
497.4272331
|
6262.407026
|
538.86
|
6219
|
|
01/04/2023
|
342.0636413
|
6303.839793
|
538.86
|
6262.407026
|
|
01/05/2023
|
420.4345692
|
6500.636151
|
538.86
|
6303.839793
|
|
01/06/2023
|
360.0698326
|
6619.061582
|
538.86
|
6500.636151
|
|
01/07/2023
|
376.2096816
|
6797.851749
|
538.86
|
6619.061582
|
|
01/08/2023
|
356.3830067
|
6960.502068
|
538.86
|
6797.851749
|
|
01/09/2023
|
484.2190118
|
7142.979061
|
538.86
|
6960.502068
|
|
01/10/2023
|
525.0736772
|
7197.620049
|
538.86
|
7142.979061
|
|
01/11/2023
|
541.5184192
|
7211.406372
|
538.86
|
7197.620049
|
|
01/12/2023
|
415.6753953
|
7208.747953
|
538.86
|
7211.406372
|
The total Hours at Month starting should be the previous row of Total Hours EOM (I guess this can be done in the front end using above function).
Kindly help me to get the above output as i have tried but could not succeed, its a bit urgent as the deliverables are very near, any help would be highly appreciated.
Kind Regards,
Pavan
