8 Replies Latest reply: Apr 10, 2015 7:34 PM by Vishnu Sreekumar

Issue with pivot table Dimensionality() calculation

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

• Re: Issue with pivot table Dimensionality() calculation

Try this expr

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

• Re: Issue with pivot table Dimensionality() calculation

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

• Re: Issue with pivot table Dimensionality() calculation

Hi Vishnu,

Write the expression as :

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

Find your attached application with correction

Thanks and Regards,

Ankita

• Re: Issue with pivot table Dimensionality() calculation

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

• Re: Issue with pivot table Dimensionality() calculation

tested,it's working

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

• Re: Issue with pivot table Dimensionality() calculation

Hi Vishnu,

sum(if(Count>2,

Count,

0))

Saritha Xavier

• Re: Issue with pivot table Dimensionality() calculation

hi.

if() helped! try using this in you expression

sum(

if(count > 2,

count,

!0)

)

• Re: Issue with pivot table Dimensionality() calculation

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.