topic Re: Chart Avarage in QlikView

Chart Avarage

Thu, 27 Nov 2014 05:14:20 GMT

Dear All

I have created below pivot chart .

Open Documents	Status	Count	Amount	Ageing
DOC1	Status1	182	3,218,498	27
DOC1	Status2	77	1,276,406	2
DOC1	Status2	18	210,981	8
DOC1	Total	277	4,705,885	19
DOC2	Status1	1	25,276	27
DOC2	Total	1	25,276	27
Total		278	4,731,161	19

where in Ageing is calculates as :

avg(Today()-[Upload date])

At the total row the average is not coming accurate. Hignlighted in bold.

In first case it should be 12.

Thanks in advance.

Re: Chart Avarage

ashwanin — Thu, 27 Nov 2014 05:19:45 GMT

You are using Upload date in Aging.

Please check, what is the last Uploaded Date.

Re: Chart Avarage

jonathandienst — Thu, 27 Nov 2014 05:23:08 GMT

12 is a simple average of the 3 numbers, whereas 19 is the correct weighted average of the underlying data. If for some reason, you need the simple average, then you need to do something like this:

=Sum(Aggr(Avg(<your age expression>),[Open Documents],Status))

(insert your expression for age; the aggr() needs all the dimensions in your chart)

HTH

Jonathan

Re: Chart Avarage

anbu1984 — Thu, 27 Nov 2014 05:27:56 GMT

Can you post sample app

Re: Chart Avarage

jagan — Thu, 27 Nov 2014 05:39:55 GMT

Hi,

Try including Update Date as dimension in the chart?

Regards,

Jagan.

Re: Chart Avarage

Thu, 27 Nov 2014 06:43:47 GMT

HI All

Thanks for suggetions.

Had made small change in formula and used like this

if(Dimensionality()=0,avg(Today()-[Upload date]), avg(aggr(avg(Today()-[Upload date]),Status,Open Documents)))

which is giving perfect result.

Open Documents	Status	Count	Amount	Ageing
DOC1	Status1	182	3,218,498	27
DOC1	Status2	77	1,276,406	2
DOC1	Status3	18	210,981	8
DOC1	Total	277	4,705,885	13
DOC2	Status1	1	25,276	27
DOC2	Total	1	25,276	27
Total		278	4,731,161	19