Hi dear Qlik-Community!
maybe somebody can help me. I try to create a forecast (1.5 years, so until end of 2019) in Qlik. Here is my problem:
Imagine, there are customer which get a sales invoice every 3 months. I need to show the planned sales on monthly basis, like a monthly forecast (every column is a month):
![Unbenannt2.PNG](/legacyfs/online/214289_Unbenannt2.PNG)
So far, my expressionis like:
"IF 'Period Start Date' is between 1st and 31th of January, 'Amount';
Next month would be April (Sales Invoice in January, so no Invoice in February and March). For April I just added '93' to the starting date (93 days are about 3 months):
"IF 'Period Start Date +93' is between 1st and 30th of April, 'Amount';
for July, same procedure - 93 multiplied with 2 - which are 6 months into the future:
"IF 'Period Start Date +93*2' is between 1st and 30th of April, 'Amount';
...and so on and so forth...
So for April, my statement looks like this:
if ( 'Period Start Date' is > 01.04.2018 and < 30.04.2018, Amount,
if ('Period Start Date' + 92 ' is > 01.04.2018 and < 30.04.2018 + 92, Amount,
if ('Period Start Date' + 92*2 ' is > 01.04.2018 and < 30.04.2018 + 92*2, Amount,
.....
))))
In this way, the amount gets shown all 3 months, which isn't wrong - it does work, but I feel like it is not a very prefessional and reliable solution (and I am aware of the fact that '+93' is wrong on a longterm sight, but for 1.5 years it is correct).
Any hints? Just fyi - my master calender has quarters, but I wouldn know how to use them.
Thank you very much for all different kind of help, if you see a more easier way to create this forecast, please tell me ![](/legacyfs/online/emoticons/happy.png)
Jakob