11 Replies Latest reply on Jul 20, 2018 2:40 PM by jon rios

# how to change calculation to work in CASE statement

i have a multiple CASE WHEN THEN statement, i added another WHEN THEN, i get an error "cannot mix aggregate & non-aggregate comparisons or results in CASE"

if i remove the SUM, the division doesnt work... what can i do to this formula to make it work with CASE STATEMENT?

• ###### 1. Re: how to change calculation to work in CASE statement

Try If Then Else. It should Work

• ###### 2. Re: how to change calculation to work in CASE statement

where would i put the ELSE? in the statement above? what would be the else?

• ###### 3. Re: how to change calculation to work in CASE statement

USE Nesting IF with CASE

CASE [EXPRESSION]

WHEN "xx" THEN

IF [EXPRESSION 1] THEN "1"

ELSEIF [EXPRESSION 2] THEN "2"

END

END

---------------OR------------

• ###### 4. Re: how to change calculation to work in CASE statement

Again, Can you post your Formula and the Screenshot of your error?

• ###### 5. Re: how to change calculation to work in CASE statement

i still get the error

• ###### 6. Re: how to change calculation to work in CASE statement

That is Because, You Have aggregated 36+60 MOB ADB and not Others like ADB Per Active Etc.

Put ATTR or Sum Before all Measures in RED

• ###### 7. Re: how to change calculation to work in CASE statement

thanks Deepak, ill try that along with Bhargav response above...

But WHY doesnt Tableau just provide the output?  what is the logic? that everything HAS to be and agg or non?

• ###### 8. Re: how to change calculation to work in CASE statement

The Logic is in the Error You Received. Either use all aggregated values or Non aggregated. You Can't do mixing

• ###### 9. Re: how to change calculation to work in CASE statement

im not sure what your talking about in your post....  can you try to write out the example using my example?

not the whole things just the nested IF

like this???

Case [KPIs (Usage Risk+Vintage) ]

WHEN  'Average Daily Balance per Open Accounts' then

if 'Average Daily Balance per Open Accounts' then [ADB per Active] else

if 'Average Daily Balance per Open Accounts' then [36 + 60 MOB ADB]

END

WHEN ' blah" THEN 'Blah'

WHEN --- THEN ----

END

• ###### 10. Re: how to change calculation to work in CASE statement

I don't Know your Exact Situation but If You have one aggregated and other non aggregated then you should have

Case [KPIs (Usage Risk+Vintage) ]

WHEN  'Average Daily Balance per Open Accounts' then        ATTR(  [ADB per Active])

WHEN 'Average Daily Balance per Open Accounts' then   [36 + 60 MOB ADB]

END