Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I need you help here.
I have a pivot table with two dimensions as shown here. "Sum" is an expression which is Sum(Count). All's good till here.
Now, my requirement is to substitute 0 to any item for which the sum =< 2. So I modified the expression as expression
if(Sum(Count)>2,
Sum(Count),
0)
and those items affected by this change are shown by the yellow highlighted rows in below image
The problem here is, even though the expression resulting in value 2 (of corresponding to "item 2") is replaced by 0, the partial sum did not change and it still shows 19 instead of 17.
I am attaching the sample file I am working with. Any help would be much appreciated.
Regards,
Vishnu
You need an Aggr() to get the correct sum of rows value in a pivot. It will look like this:
Sum(Aggr(if(Sum(Count)>2, Sum(Count), 0), Category, Item))
HTH
Jonathan
Try this expr
=Sum(If(Aggr(Sum(Count),Category,Item) >2,Count))
Use this instead :
if(Dimensionality()=1,sum(if(Count>2,(Count),0)),if(Sum(Count)>2,Sum(Count),0))
Hi Vishnu,
Write the expression as :
=sum(if(Count>2,Count,0))
Find your attached application with correction
Thanks and Regards,
Ankita
You need an Aggr() to get the correct sum of rows value in a pivot. It will look like this:
Sum(Aggr(if(Sum(Count)>2, Sum(Count), 0), Category, Item))
HTH
Jonathan
tested,it's working
=Sum(If(Aggr(Sum(Count),Category,Item)>2,Count))
Hi Vishnu,
Please try this expr,
sum(if(Count>2,
Count,
0))
Saritha Xavier
hi.
if() helped! try using this in you expression
sum(
if(count > 2,
count,
!0)
)
Thanks all.
Actually all of the answers worked. But for my actual scenario, I found
Sum(Aggr(if(Sum(Count)>2, Sum(Count), 0), Category, Item)) as the best fit.