topic Calculation Avg based on difference of rows in QlikView

Calculation Avg based on difference of rows

Tue, 12 Feb 2013 16:38:34 GMT

Hello All,

I need help from you experts.

I have a table like below

In this table Column 3 is the difference of column1 rows.(Below - Above Row) i.e., ( 0-97 - 0.85 = 0.12 ).

What i need is, to calculate the average of Column3(sum(Column3) / Count(Column3)) . I have tried many ways. but am not getting proper result.

I have to use this average value in an another formula.

Help me if you people have any idea on this.

Thanks in Advance

Re: Calculation Avg based on difference of rows

swuehl — Tue, 12 Feb 2013 16:57:36 GMT

Maybe like attached (for a chart based solution, it might be better to do all this in the script, if you don't need to be selection sensitive)?

Re: Calculation Avg based on difference of rows

Tue, 12 Feb 2013 18:13:31 GMT

Hi Stefan,

Thanks for your help. Yeah, it is good if we give in script level, but in chart only am calculating the difference and i have to calculate the average of this difference value. I don't have column1,column2 n 3 as dimension, All are expressions.

Any way thanks Stefan, for your reply. based on your reply i got an idea.

Re: Calculation Avg based on difference of rows

vincent_ardiet — Wed, 13 Feb 2013 09:02:49 GMT

Hi,

Have you try to use an aggr function?

For example : avg(aggr(nodistinct Column1-above(total Column1),CommonYear, CommonMonth))

Regards,

Vincent

Re: Calculation Avg based on difference of rows

er_mohit — Wed, 13 Feb 2013 09:15:45 GMT

try this

sum(TOTAL Column3)/Count(TOTAL Column3)

see the attached file