Dynamic Forecast Slab

[TS08]

Hi,

Can anyone help me defining Forecast slab based on "Forecast Date" and Today().

I have tried something similar in Power BI but not able to get the desired result in Qlik.

Below is the business logic I am trying to develope:

e.g. Forecast Date < today() i.e. 23^rd Sep and Prior then outcome should be ‘5) Forecast Missed’,

Forecast Month = ‘Blank’ then outcome should be ‘6) No Forecast’,

Forecast Month = today's month i.e. September then outcome should be ‘1) Sep-22’,

Forecast Month - today's month = 1 i.e. October then outcome should be ‘2) Oct-22’,

Forecast Month - today's month = 2 i.e. November then outcome should be ‘3) Oct-22‘,

Or else

‘4) >3 Months’

Formula I tried: -

if(Isnull([Delivery Forecasted Date]),'5) No Target',

if([Delivery Forecasted Date]< TODAY(), '5) Target Missed',

if(MONTH([Delivery Forecasted Date],'MM') = MONTH(today(),'MM'), date(monthstart([Delivery Forecasted Date])),'1) MMM-YY',

if((month([Delivery Forecasted Date],'MM') - MONTH(today(),'MM')) = 1 or (month([Delivery Forecasted Date],'MM') - MONTH(today(),'MM')) = -11, date(monthstart([Delivery Forecasted Date])),'2) MMM-YY',

if((MONTH([Delivery Forecasted Date],'MM') - MONTH(today(),'MM')) = 2 or (month([Delivery Forecasted Date],'MM') - MONTH(today(),'MM')) = -10, date(monthstart([Delivery Forecasted Date])),'3) MMM-YY',

'4) > 3 Months')))))

Thanks

[morgankejerhag]

What error are you getting? Here are a couple of points/questions:

• You are missing ending parenthesis at the end of the expression. Each if statement should be closed.
• Is Forecast Date in true date format?
• Instead of comparing months with 1 and -11, you can use if InMonth([Forecast Date],today(),1) More info here

[TS08]

Not sure, what am I doing wrong here but everytime I try anyother syntax not getting desired output. 

Can anyone help me break this in code?

e.g. If Forecast Date < today() i.e. 23^rd Sep and Prior then outcome should be ‘5) Forecast Missed’,

If Forecast Month = ‘Blank’ then outcome should be ‘6) No Forecast’,

If Forecast Month = today's month i.e. September then outcome should be ‘1) Sep-22’,

If Forecast Month - today's month = 1 i.e. October then outcome should be ‘2) Oct-22’,

If Forecast Month - today's month = 2 i.e. November then outcome should be ‘3) Oct-22‘,

Or else

‘4) >3 Months’

Thanks