Sum of average value in Pivot chart

[tamilarasu]

Hi All,

In pivot table I need to find the total sum of average for all tables. But I got wrong value for total sum of average for each day.

Please see the attachment. Can anyone help me on this.

Thank you in advance.

Kind Regards,

Tamil

[jagan]

Hi,

Check this expression

Sum(Aggr(Avg(Sales), Country, day))/Count(Distinct day)

Regards,

Jagan.

[tamilarasu]

Hello Mario,

Thanks and your answer really helpful. I have modified your expression like below (with Jagan's solution) but the values seems wrong. I am not sure where I am missing

IF (ColumnNo()=0, RangeSum(top(avg(Sales),1,NoOfRows())),top(Sum(Aggr(Avg(Sales), Country, day))))

[#4]

ColumnNo() returns the values from 1 to N for the Expressions only. Value 0 doesn't exists.

[#5]

IF (Dimensionality()=0, RangeSum(top(avg( Sales),1,NoOfRows())))

and

IF (ColumnNo()=1, RangeSum(top(avg(Sales),1,NoOfRows())),top(Sum(Aggr(Avg(Sales), Country, day))))

will give you similar results.

[#6]

Will give you similar results but just for the 1st. Expression Column 🙂

[tamilarasu]

Hello Jagan,

I go the count but average of days changed to sum.

Any Idea?

Kind Note: Is it possible to change the below expression

IF (ColumnNo()=0, RangeSum(top(avg(Sales),1,NoOfRows())),top(Sum(Aggr(Avg(Sales), Country, day))))

[tamilarasu]

I am new to qlikview and I am not aware about this. I can see the column number is 0 for total field. I thought it will return from 0 to N. Did u check the attached file as I am getting different values.

[#9]

Tamil I got YOu:

put this

IF (Dimensionality()=0, IF (ColumnNo()=0, RangeSum(top(avg(Sales),1,NoOfRows())),top(Sum(Aggr(Avg(Sales), Country, day)))) ,Avg(Sales))

[#10]

Yes, it is possible in Average expression write:

IF (Dimensionality()=0, IF (ColumnNo()=0, RangeSum(top(avg(Sales),1,NoOfRows())),top(Sum(Aggr(Avg(Sales), Country, day)))) ,Avg(Sales))

[#11]

but, with my solution for all the columns and not only for column 0:

write this:

IF (Dimensionality()=0, IF (Dimensionality()=0, RangeSum(top(avg( Sales),1,NoOfRows()))) ,Avg(Sales))

in the Average Expression: