How to set value to zero when the denominator in aggregate_aware is zero

[Former Member]

Hi,

Please help me on this. How do I set the value to zero for each aggregate awareness when the denominator is zero. Below is my select statement in the universe measure.

@Aggregate_Aware(sum(column1/column2),sum(column3/column4),sum(column5/column6))

Thank you in advance.

Rose

[Former Member · Accepted solution]

Hi Rosemarie,

You can use a case statement

case when column2 = 0 then 0 else column1/column2 end

This can be embedded directly in your Aggregate_Aware function

@Aggregate_Aware(sum(case when column2 = 0 then 0 else column1/column2 end),sum(column3/column4),sum(column5/column6))

(Note you will need to change all of them )

but for clarity it might be better to create a new object, my_object

defined as

case when column2 = 0 then 0 else column1/column2 end

and then

@Aggregate_Aware(sum(@select(my class\my object)),sum(column3/column4),sum(column5/column6))

Hope this helps

Alan

[Former Member]

Hi Alan,

Thank you so much for your help. The code is working fine.

More power to you.

Thank you.

Rose