Issue with pivot table Dimensionality() calculation

[vishnus85]

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

[anbu1984]

Try this expr

=Sum(If(Aggr(Sum(Count),Category,Item) >2,Count))

[Anonymous]

Use this instead :

if(Dimensionality()=1,sum(if(Count>2,(Count),0)),if(Sum(Count)>2,Sum(Count),0))

[ankitaag]

Hi Vishnu,

Write the expression as :

=sum(if(Count>2,Count,0))

Find your attached application with correction

Thanks and Regards,

Ankita

[jonathandienst · Accepted solution]

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

[buzzy996]

tested,it's working

=Sum(If(Aggr(Sum(Count),Category,Item)>2,Count))

[#7]

Hi Vishnu,

Please try this expr,

sum(if(Count>2,

Count,

0))

Saritha Xavier

[#8]

hi.

if() helped! try using this in you expression

sum(

     if(count > 2,

          count,

          !0)

     )

[vishnus85]

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.