topic Re: IF and MAX function in App Development

IF and MAX function

qlikie — Mon, 06 Jan 2020 10:41:14 GMT

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

Re: IF and MAX function

lorenzoconforti — Sat, 04 Jan 2020 23:55:43 GMT

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

)

Re: IF and MAX function

stevedark — Sun, 05 Jan 2020 15:47:11 GMT

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

Re: IF and MAX function

qlikie — Mon, 06 Jan 2020 10:42:51 GMT

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.

Re: IF and MAX function

lorenzoconforti — Mon, 06 Jan 2020 11:00:30 GMT

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?

Re: IF and MAX function

qlikie — Mon, 06 Jan 2020 11:05:14 GMT

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