topic Re: aggregated value in pivot table total in QlikView

aggregated value in pivot table total

Tue, 14 Jan 2014 10:34:54 GMT

Hello

I have an aggregated value on a higher lever dimension that should repeated on a lower level dimension, the total should not be the sum of the repeated value but rather the value itself.

the exemple is described bellow in the column expression 1 in the attached file

Thanks in advance for your help

Re: aggregated value in pivot table total

Tue, 14 Jan 2014 11:08:06 GMT

Try using Distinct...

Re: aggregated value in pivot table total

Tue, 14 Jan 2014 11:13:05 GMT

you mean sum (distinct field)?

Re: Re: aggregated value in pivot table total

Tue, 14 Jan 2014 11:23:52 GMT

Sorry distinct will not help you, attached app may help u ..

(RowNo()>=1,sum(Expression1),sum(TOTAL Expression1))

(RowNo()>=1,sum(Expression1),sum(DISTINCT Expression1))

Re: aggregated value in pivot table total

Clever_Anjos — Tue, 14 Jan 2014 11:42:54 GMT

You can use a combination of

sum( TOTAL <Dim1,Dim2, <dim3>> value)

TOTAL = All records in table

<dim1> will return aggregate by dim1

<dim1,dim2> will return aggregate by dim1,dim2

Re: aggregated value in pivot table total

vikasmahajan — Tue, 14 Jan 2014 11:45:27 GMT

Try

Aggr(distinct sum( TOTAL <Dim1,Dim2, <dim3>> value))

Vikas

Re: aggregated value in pivot table total

Tue, 14 Jan 2014 14:02:48 GMT

Actually I have found the answer, if you ever meet this sort of requirement you need to adjust the expression using dimensionality Function in my case

if(Dimensionality()=1

or Dimensionality()=0, sum([field]), [field])

Thank you very much for your answers