7 Replies Latest reply on Nov 27, 2016 5:58 AM by JayC

Mixing aggregate and non-aggregate measures in CASE statement - Help Needed Plz

Hi All,

I am stuck in mixing Agg & Non Agg measures. Have tried solutions from few threads with no success before posting as a question here.
Have attached mock up version.

Issue arises when i try to use PPT in the case stmt.

• 1. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help Needed Plz

The problem is in the field : Metric Selection Flag

As PPT is already aggregated, you need to aggregate the others. so you need to wrap the other fields in an aggregation function.

Here's an example.

CASE [Metric Selection]

when 1 then SUM([Txns])

when 2 then SUM([Revenue])

when 3 THEN SUM([PRN])

when 4 then [PPT]

END

You need to choose the right aggregation function based on your data.

• 2. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help Needed Plz

To add on top of Marc's response one option is to aggregate everything, the other option is to convert Metric Selection Flag to an LOD expression if possible (which are aggregates but are treated as row level in calculations). In some cases this may not be possible e.g. if it is a table calc.

• 3. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help Needed Plz

Thx for responding to my question.

I did try matching Agg+NonAgg as per my data before posting the Qstn but the measures (a,b,c) instead showed error of Agg+NonAgg mix again.

Tried fixing them by changing formula's from

IF [Final Status]= "IMPACTED - PENDING" THEN [Metric Selection Flag]

ELSE 0

END

to

IF attr([Final Status])= "IMPACTED - PENDING" THEN [Metric Selection Flag]

ELSE 0

END

but the resulting output is 0 for any metric selected.

With no success i ended up posting the Question in the forum.

May be I am asking more....but if possible can u plz replicate the fix in the attached .Twbx and share.?

• 4. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help Needed Plz

Here's the workbook with the calculations.

• 5. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help Needed Plz

Hi Marc,
I don't have v10 installed. Can you please convert to v9.3.?

• 6. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help Needed Plz

Here are the field with their formula :

Metric Selection Flag :

CASE [Metric Selection]

when 1 THEN SUM([Txns])

when 2 THEN SUM([Revenue])

when 3 THEN SUM([PRN])

when 4 THEN [PPT]

END

a :

IF [Final Status]= "IMPACTED - COMPLETE" THEN

{ INCLUDE [Final Status] : [Metric Selection Flag]}

ELSE 0

END

b :

IF [Final Status]= "IMPACTED - PENDING" THEN

{ INCLUDE [Final Status] : [Metric Selection Flag]}

ELSE 0

END

c :

IF [Final Status]= "IMPACTED - REJECTED" THEN

{ INCLUDE [Final Status] : [Metric Selection Flag]}

ELSE 0

END

• 7. Re: Mixing aggregate and non-aggregate measures in CASE statement - Help Needed Plz

Thanks a lot for your help.

Worked like charm and also equally nice things for me to learn.