topic Re: If exists within an expression in QlikView

If exists within an expression

fanninam — Wed, 25 Nov 2020 16:16:04 GMT

I am trying to figure out how to show actuals if actuals exist otherwise show forecast. I want to do this within the expression, not in the script. I have tried: Sum({$<Dept={'cc234'}>}if (Actual_Amount, Actual_Amount, Forecast))

Can anyone help me with this?

Re: If exists within an expression

swuehl — Mon, 21 Mar 2016 17:41:09 GMT

And what do you get with your expression?

Doesn't it work?

It would probably be helpful if you could explain your data model a bit more detailed or even better, if you could upload a small sample QVW?

Re: If exists within an expression

fanninam — Mon, 21 Mar 2016 18:11:31 GMT

Hi Swuehl,

What happens when I use "Sum({$<Dept={'cc234'}>}if (Actual_Amount, Actual_Amount, Forecast)) " is it shows the forecast only. The data model is pulling in actual spend and forecast for the remainder of the year.

Re: If exists within an expression

fanninam — Mon, 21 Mar 2016 18:35:53 GMT

Swuehl,

I also tried "Sum({$<Dept={'cc234'}>}ALT(Actual_Amount, Forecast)) " and it shows the forecast only as wel.

Re: If exists within an expression

swuehl — Mon, 21 Mar 2016 20:00:57 GMT

I don't see any obvious why your expression shouldn't work,

Are you sure that Dept cc234 shows numeric actual amount values?

Could you post some sample lines of records?

Re: If exists within an expression

Peter_Cammaert — Mon, 21 Mar 2016 23:17:14 GMT

Knowing nothing about the dimensions you are using, maybe you can try with:

=IF(Sum({$<Dept={'cc234'}>}Actual_Amount), Sum({$<Dept={'cc234'}>}Actual_Amount), Sum({$<Dept={'cc234'}>} Forecast))

Re: If exists within an expression

jagan — Tue, 22 Mar 2016 03:24:19 GMT

Hi,

Try like this

If(Sum({$<Dept={'cc234'}>}Actual_Amount) > 0, Sum({$<Dept={'cc234'}>}Actual_Amount) , Sum({$<Dept={'cc234'}>}Forecast))

Regards,

Jagan.

Re: If exists within an expression

fanninam — Tue, 22 Mar 2016 12:37:15 GMT

Thank you so much. This is working.