5 Replies Latest reply on Dec 9, 2019 8:32 AM by Jim Dehner

getting error like(can't mix aggregate and Non-aggregate Comparison  or result's using CASE Expressions )

Hi All,

I have program filed in that Various program's as per that ,I was created the different calculations (please see the below Metric's)

when i will change the filter's (Program,Time Period ) based on below calculations need to be apply .

Calculations for program

1. POST = Mailed Qty * 0.47
2. PRE3001 and PRE3002 = Total Mailed  * 0.45     ( Total Mailed= COUNTD((if [Control]=0 THEN [Mail ID] ELSE NULL end))  )
3. MONTHLY = Total Mailed  * 0.403
4. WEEKLY = Total Mailed * 0.40

Calculations for M.Cost

CASE [Program] WHEN 'POST' THEN [Post]

WHEN 'PRE3001' THEN [PRE3001 and PRE3002]

WHEN 'PRE3002' THEN [PRE3001 and PRE3002]

WHEN 'NCOA_MONTHLY' THEN [NCOA Monthly]

WHEN 'NCOA_WEEKLY' THEN [Weekly]

END

I want to create the M.Cost , for that i was using the above Calculations ,by using this i am getting error   .

Is there any way to get this calculations. i have attached twbx file and snap of error .

Regards

S.Srinivas,

• 1. Re: getting error like(can't mix aggregate and Non-aggregate Comparison  or result's using CASE Expressions )

Hi Srinivas,

Please use this formula

CASE [Program] WHEN 'POST' THEN  sum([Post])

WHEN 'PRE3001' THEN [PRE3001 and PRE3002]

WHEN 'PRE3002' THEN [PRE3001 and PRE3002]

WHEN 'NCOA_MONTHLY' THEN sum([NCOA Monthly])

WHEN 'NCOA_WEEKLY' THEN sum([Weekly])

END

Please let me know if this helps.

Else please downgrade workbook to 2018.1 version.

Thanks,

Shrten

• 2. Re: getting error like(can't mix aggregate and Non-aggregate Comparison  or result's using CASE Expressions )

good morning

all of these measures are aggregated note the agg

this is driving many of them Countd is an aggregate

so what to do wrap program in attr

CASE  attr( [Program] )  WHEN 'POST' THEN [Post]

WHEN 'PRE3001' THEN [PRE3001 and PRE3002]

WHEN 'PRE3002' THEN [PRE3001 and PRE3002]

WHEN 'NCOA_MONTHLY' THEN [NCOA Monthly]

WHEN 'NCOA_WEEKLY' THEN [Weekly]

END

and you book does not recognize Weekly

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 3. Re: getting error like(can't mix aggregate and Non-aggregate Comparison  or result's using CASE Expressions )

Use Attr for programme .

CASE attr( [Program] ) WHEN 'POST' THEN [Post]

WHEN 'PRE3001' THEN [PRE3001 and PRE3002]

WHEN 'PRE3002' THEN [PRE3001 and PRE3002]

WHEN 'NCOA_MONTHLY' THEN [NCOA Monthly]

WHEN 'NCOA_WEEKLY' THEN [Weekly]

END

• 4. Re: getting error like(can't mix aggregate and Non-aggregate Comparison  or result's using CASE Expressions )

Hi Jim,

thanks fro the Solutions here

But as per you calculations i am not getting Grand total , is there any way to get that

I have tried in new sheet as well .

Regards,

S.Srinivas

• 5. Re: getting error like(can't mix aggregate and Non-aggregate Comparison  or result's using CASE Expressions )

That is a separate question not related to the syntax issue youi had with your case statement

and has to do with he order of operations and grand totals on aggregates -

suggest your formulate it as a separate question and post again

Jim