topic Re: Issue with pivot table Dimensionality() calculation in QlikView

Issue with pivot table Dimensionality() calculation

vishnus85 — Fri, 10 Apr 2015 08:31:46 GMT

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),

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

anbu1984 — Fri, 10 Apr 2015 08:51:45 GMT

Try this expr

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

Re: Issue with pivot table Dimensionality() calculation

Anonymous — Fri, 10 Apr 2015 09:04:42 GMT

Use this instead :

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

Re: Issue with pivot table Dimensionality() calculation

ankitaag — Fri, 10 Apr 2015 09:17:52 GMT

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

jonathandienst — Fri, 10 Apr 2015 09:39:56 GMT

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

buzzy996 — Fri, 10 Apr 2015 09:50:40 GMT

tested,it's working

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

Re: Issue with pivot table Dimensionality() calculation

Fri, 10 Apr 2015 10:37:57 GMT

Hi Vishnu,

Please try this expr,

sum(if(Count>2,

Count,

0))

Saritha Xavier

Re: Issue with pivot table Dimensionality() calculation

Fri, 10 Apr 2015 10:59:44 GMT

hi.

if() helped! try using this in you expression

sum(

if(count > 2,

count,

!0)

)

Re: Issue with pivot table Dimensionality() calculation

vishnus85 — Fri, 10 Apr 2015 23:34:11 GMT

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.