Forecast - quarterly Bill

[jakobjosef]

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):

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

Jakob