4 Replies Latest reply: Dec 18, 2017 11:08 AM by David Ambrozie

# How to use if condition inside sum(i.e nested sum)

Hello,

I want to sum the output of this entire expression i.e

if(sum(if(aggr(Count({\$<[Customer Name]={'ABC'},Sector={'XYZ'}>}[Emp ID]),[Customer Name],Sector)>0,1,0))>3,1,0)

Use the above expression inside sum() function.

Kindly help.

• ###### Re: How to use if condition inside sum(i.e nested sum)

Hi Pooja, maybe:

if(sum(aggr(if(Count({\$<[Customer Name]={'ABC'},Sector={'XYZ'}>}[Emp ID])>0,1,0),[Customer Name],Sector))>3,1,0)

• ###### Re: How to use if condition inside sum(i.e nested sum)

Its not working..

Actually i want to use the above expression inside sum function in order to sum the output of the expression...

but its showing an error of nested aggregation not allowed....

• ###### Re: How to use if condition inside sum(i.e nested sum)

That expression will return only a single 1 or 0, there is nothing to sum, it needs a dimension or another aggr that returns differents 0's and 1's based on another field

• ###### Re: How to use if condition inside sum(i.e nested sum)

Hi Pooja,

First of all, using aggr in expressions is very slow and hard to read. You can use TOTAL qualifier to group by dimension (e.g. Count(TOTAL <[Customer Name], Sector> [Emp ID]) See When should the Aggr() function NOT be used?

Based on you expression, I suppose you want to sum up the output of the count. But why do you need to do that? You should be able to get the result by using only the count expression. Can you provide a sample or an Excel mock-up of what are you trying to calculate?

Regards,

David