IF and MAX function

[qlikie]

Hi, 

I'm struggling to combine the below into one using an IF function. I have a filter #months to forecast and I want the KPI to change based on what I select. For each month there are different recommendations with different associated values.  I am able to get the right KPIs for month 1, 2 and 3 when I use the below; however, I need to use an IF function to combine it into one field. Thanks for your help in advance!

Month 1 KPI: 

Money(max({<Date={'Mar-19'}, [#months to predict]={'1 month'}>+<[#months to predict]={'2 month'}>+<[#months to predict]={'3 month'}>}
[Predicted Sales value]/1000000)
-
max({<Date={'Mar-19'},[#months to predict]={'1 month'}>+<[#months to predict]={'2 month'}>+<[#months to predict]={'3 month'},
[Predicted Sell Out Sales value]=,[actual_or_forecast]={'F'}>} [Sales value in the past X no. of months]/1000000),
'#0.00 M')

Month  2 KPI:

Money(max({<Date={'Apr-19'}, [#months to predict]={'2 month'}>}
[Predicted Sales value]/1000000)
-
max({<Date={'Apr-19'},[#months to predict]={'2 month'},
[Predicted Sell Out Sales value]=,[actual_or_forecast]={'F'}>} [Sales value in the past X no. of months]/1000000)
, '#0.00 M')

 Month 3 KPI:

Money(max({<Date={'Mar-19'}, [#months to predict]={'3 month'}>}
[Predicted Sales value]/1000000)
-
max({<Date={'Mar-19'},[#months to predict]={'3 month'},
[Predicted Sell Out Sales value]=,[actual_or_forecast]={'F'}>} [Sales value in the past X no. of months]/1000000)
,
'#0.00 M')

UPDATE: I'VE SIMPLIFIED THE ABOVE CALCULATIONS 

[lorenzoconforti]

Use pick/match in your KPI object. Check what the user has selected in months to forecast and, according to the value, calculate the relevant KPI. In the example below replace "Month x KPI" with your expressions; alternatively upload your app so that we can have a look at it

=pick(match(only([ #months to forecast]),1, 2, 3),

Month 1 KPI,

Month 2 KPI,

Month 3 KPI

)

[stevedark · Accepted solution]

The thing to do here is to put each of the KPI expressions into a variable, which then makes the pick/match expression much more manageable.

The new container objects in Sense would allow you to have a display condition on three different KPI objects and you could have a different expression on each. This may be a better way of dealing with it, as it gives you flexibility around exactly how each KPI should look.

Steve

[qlikie]

Thanks a lot. I've tried this; however, I seem to only be getting the first value when I choose '1 month'. When I select '2 months' or '3 months' there's no value at all. 

[lorenzoconforti]

Have you found a solution (as you've marked you have) or are you still having problems (as you are saying in your comment)?

If you are still having issues, can you upload your dashboard and clarify what you are trying to achieve?

[qlikie]

I have, thanks a lot! A combination of pick/match and putting KPIs into variables worked perfectly in a container object.