If exists within an expression

[fanninam]

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?

[swuehl]

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?

[fanninam]

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.

[fanninam]

Swuehl,

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

[swuehl]

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?

[Peter_Cammaert · Accepted solution]

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

[jagan]

Hi,

Try like this

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

Regards,

Jagan.

[fanninam]

Thank you so much.  This is working.