topic Average help in QlikView

Average help

ajaykumar1 — Tue, 29 Dec 2015 13:40:29 GMT

Hi Everyone,

Greetings of the day !.

I need some help to calculate the average formula with below mentioned output format.

I tried like this ; avg(aggr(sum(amount),[week]))

Please find the attached sample data.

Input Data :

ID	Week	amount
1	37	5500
1	38	20000
1	45	13000
1	47	24000
1	50	24000
5	40	15000
5	44	17000
10	43	50000
12	46	80000
12	47	70000

Week is my dimension.I taken in Pivot table for to fulfil in horizontal way.

Out put :

ID	Week	37	38	40	43	44	45	46	47	50	Grand Total	Average
1		5500	20000				13000		24000	24000	86500	17300
5				15000		17000					32000	16000
10					50000						50000	50000
12								80000	70000		150000	75000
Total	5500	20000	15000	50000	17000	13000	80000	94000	24000	318500

More Thanks,

Ajay

Re: Average help

swuehl — Tue, 29 Dec 2015 13:44:26 GMT

I think you may need to add ID to your aggr() dimensions:

avg(aggr(sum(amount),[week], ID))

Re: Average help

sunny_talwar — Tue, 29 Dec 2015 13:46:25 GMT

This?

Expression: =Avg(Aggr(Sum(amount), ID, Week))

Re: Average help

ajaykumar1 — Tue, 29 Dec 2015 13:51:38 GMT

Thanks for quick reply....But its not giving expected output.

I want average also...Upto grand total i got it.

Re: Average help

Anonymous — Tue, 29 Dec 2015 13:52:08 GMT

PFA..

Re: Average help

sunny_talwar — Tue, 29 Dec 2015 13:52:50 GMT

You want average and grand total both?

Re: Average help

ajaykumar1 — Tue, 29 Dec 2015 13:56:42 GMT

Yes....I want Grand total and Average last right side.

Your earlier output is right but i need grand total also...Your average is perfect.

Re: Average help

sunny_talwar — Tue, 29 Dec 2015 14:15:45 GMT

This:

Script:

Table:

LOAD ID,

Week,

amount

FROM

Avg_Help.xlsx

(ooxml, embedded labels, table is [Data ]);

Dim:

LOAD * Inline [

Dim

];

Pivot Table

Dimensions:

=Pick(Dim, Week, 'Total')

Dimension Color Expression: If(Pick(Dim, Week, 'Total') = 'Total', RGB(245,245,245))

Dimension Text Format: =If(Pick(Dim, Week, 'Total') = 'Total', '<B>')

Expression:

=If(Pick(Dim, Week, 'Total') = 'Total', Sum(DISTINCT Aggr(NODISTINCT Sum(amount), ID)), Avg(Aggr(Sum(amount), ID, Week)))

Background color expression:

If(Pick(Dim, Week, 'Total') = 'Total', RGB(245,245,245))

Credit to Kush141087 for introducing this Pick technique to me.

Best,

Sunny

UPDATE:

Sort Expression for calculated Dimension:

=Pick(Dim, Week, 'Total') with Descending Sort

Re: Average help

settu_periasamy — Tue, 29 Dec 2015 14:25:33 GMT

Hi,

May be try this..

Add one dummy dimension, and use the SecondaryDimensionality() in your expression..

Like..

=if(SecondaryDimensionality()=1,sum(aggr(sum(amount),ID,Week)),

if(SecondaryDimensionality()=0,Avg(Aggr(sum(amount),ID,Week)),(sum(amount))))

Re: Average help

ajaykumar1 — Wed, 30 Dec 2015 09:15:55 GMT

Thanks Sunny.Its working fine.

I need to understand the 'Dim'Technique.

Please explain this logic when you get time.

Thanks,

Ajay

Re: Average help

Peter_Cammaert — Wed, 30 Dec 2015 09:29:22 GMT

Dim is a field belonging to a so-called date island. This means that it has no association with any of the other fields or tables in your data model. Using Dim with other fields and expressions in a chart will produce a cartesian product, meaning that both Dim values are artificially combined with everything in your table. The Pick function intercepts certain parts and returns a week number when Dim = 1, and a text value 'Total' when Dim = 2.

This is called a calculated dimension.

Best,

Peter