topic Re: Pivot table Sum of average in QlikView

Pivot table Sum of average

settu_periasamy — Mon, 28 Jul 2014 04:57:26 GMT

Hi All,

Give me the suggestion for the below screen shot. Attached the example file.

regards,

settu

Re: Pivot table Sum of average

dharmarasu — Mon, 28 Jul 2014 05:35:30 GMT

Hi,

in that above Screen shot showing correct..nothing wrong in it..

In that average showing Average(1267+1281+1277)=1275..ie (1267+1281+1277)/3=1275..Hope this helps..

Dharma

Re: Pivot table Sum of average

settu_periasamy — Mon, 28 Jul 2014 05:40:41 GMT

Hi Dharmarasu,

Already there is an average for country level. I need the sum of Country average in the dimension level. i.e

if (dimensionality()=0, sum(801 average, 802 average and 803 average). Hope i clear now..

Settu

Re: Pivot table Sum of average

jonathandienst — Mon, 28 Jul 2014 05:45:03 GMT

This expression will do it:

=Sum(Aggr(Avg(Sales), Country))

HTH

Jonathan

Re: Pivot table Sum of average

jonathandienst — Mon, 28 Jul 2014 05:48:39 GMT

Sorry, correction to the above:

If(Dimensionality() = 0, Sum(Aggr(Avg(Sales), Country)), Avg(Sales))

Re: Pivot table Sum of average

settu_periasamy — Mon, 28 Jul 2014 05:50:55 GMT

hi Jonathan,

If we give that expression, the sum value is changed. Already the sum value is correct. i need to change the dimension total only. see the arrow mark in that picture.

Re: Pivot table Sum of average

dharmarasu — Mon, 28 Jul 2014 05:57:50 GMT

pls see this attachment

Dharma

Re: Pivot table Sum of average

jonathandienst — Mon, 28 Jul 2014 06:08:03 GMT

Did you see my correction?

If(Dimensionality() = 0, Sum(Aggr(Avg(Sales), Country)), Avg(Sales))

Re: Pivot table Sum of average

settu_periasamy — Mon, 28 Jul 2014 06:14:10 GMT

Hi Dharmarasu,

i need the result like the below screen shot

Re: Pivot table Sum of average

settu_periasamy — Mon, 28 Jul 2014 06:18:41 GMT

hi jonathan,

thank you for your expression. It showing the value. but day value shows 0. see the below screen shot.

Re: Pivot table Sum of average

dharmarasu — Mon, 28 Jul 2014 06:22:48 GMT

Hi settu,

Pls see the above attached Application..it shows as it is what you required..

Dharma..

Re: Pivot table Sum of average

dharmarasu — Mon, 28 Jul 2014 06:24:17 GMT

otherwise u can use this expression in

average

=Sum(Aggr(Avg(Sales), Country,day))

Re: Pivot table Sum of average

settu_periasamy — Mon, 28 Jul 2014 06:28:08 GMT

Hi Dharmarasu,

Thank you. i checked it. average is not correct. see the red border in the above screen shot.

Instead of 6131, it should be 1226.

Settu

Re: Pivot table Sum of average

settu_periasamy — Tue, 29 Jul 2014 04:34:55 GMT

Hi Jonathan & Dharmarasu,

i got the result using the below expresson, and attached the final layout.

if(dimensionality()=0 and RowNo()=0 and ColumnNo()=0,

Sum(Aggr(Avg(Sales), Country,day1))/Count(DISTINCT day1),

if(Dimensionality()=0,

Sum(Aggr(Avg(Sales), Country,day1)),Avg(Sales)))