topic Re: Average by Group for Max QTR in QlikView

Average by Group for Max QTR

vinieme12 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

Need your help with an expression.

Need to Average Rank for the latest QTR only

So Group1=(9+4+5)/3 <<<3 being latest for Group1

and Group2=(7+5)/2 <<<<4 being latest for Group2

Group	Rec	Name	QTR	RANK
1	1111	AAAAA	1	9
1	1111	AAAAA	3	9
1	2222	BBBBBBBBB	2	8
1	2222	BBBBBBBBB	3	4
1	3333	ccccccccccccccc	3	5
1	3333	ccccccccccccccc	2	6
2	5555	yyyyyyyy	1	9
2	5555	yyyyyyyy	4	7
2	6666	iiiiiiiiiii	1	6
2	6666	iiiiiiiiiii	2	4
2	6666	iiiiiiiiiii	3	4
2	7777	mmmmmmmmmm	4	5

Re: Average by Group for Max QTR

sunny_talwar — Thu, 03 Nov 2016 13:02:42 GMT

May be this:

Sum(Aggr(If(QTR = Max(TOTAL <Group> QTR), RANK), Group, Name, QTR))/Max(QTR)

Re: Average by Group for Max QTR

vinieme12 — Thu, 03 Nov 2016 13:07:55 GMT

Thanks for your prompt response stalwar1‌

first part works, but need to divide it by the number of instances being totaled instead of Max(QTR)

Group1=(9+4+5)/3 = 6

and Group2=(7+5)/2 = 6

also is there a way to do this in Set?

Thanks

Vineeth

Re: Average by Group for Max QTR

sunny_talwar — Thu, 03 Nov 2016 13:13:40 GMT

May be this (but not sure if this is any better than the if statement approach

Sum({<Key = {"=QTR = Aggr(Max(TOTAL <Group> QTR), Key, Group)"}>} RANK)/Count({<Key = {"=QTR = Aggr(Max(TOTAL <Group> QTR), Key, Group)"}>} RANK)

Other approach

Sum(Aggr(If(QTR = Max(TOTAL <Group> QTR), RANK), Group, Name, QTR))/Count(Aggr(If(QTR = Max(TOTAL <Group> QTR), RANK), Group, Name, QTR))

Re: Average by Group for Max QTR

vinieme12 — Thu, 03 Nov 2016 13:16:33 GMT

Fab , thanks a lot stalwar1‌

Re: Average by Group for Max QTR

sunny_talwar — Thu, 03 Nov 2016 13:16:58 GMT

No problem, I am glad I was able to help bro

Re: Average by Group for Max QTR

jyothish8807 — Thu, 03 Nov 2016 13:38:35 GMT

Hi Sunny,

Can you please help me to understand the below expression ?

Sum({<Key = {"=QTR = Aggr(Max(TOTAL <Group> QTR), Key, Group)"}>} RANK)/Count({<Key = {"=QTR = Aggr(Max(TOTAL <Group> QTR), Key, Group)"}>} RANK)

How "QTR = Aggr(Max(TOTAL <Group> QTR), Key, Group)" is giving the relevant Key valu?

Thanks in advance

Br,

Re: Average by Group for Max QTR

sunny_talwar — Thu, 03 Nov 2016 13:41:40 GMT

Create a straight table with Key as dimension and three expressions

1) QTR

2) Aggr(Max(TOTAL <Group> QTR), Key, Group)

3) RANK

Wherever 1st and 2nd expressions have same value, RANK will be summed.

Re: Average by Group for Max QTR

jyothish8807 — Thu, 03 Nov 2016 13:49:12 GMT

So in the above expression "Key" will automatically identify the all the Key values where the below condition is fulfilled ?

"QTR = Aggr(Max(TOTAL <Group> QTR), Key, Group)".

Re: Average by Group for Max QTR

sunny_talwar — Thu, 03 Nov 2016 13:50:35 GMT

Exactly

Re: Average by Group for Max QTR

jyothish8807 — Thu, 03 Nov 2016 13:52:06 GMT

Thanks a lot.. learned a new thing

Br,

Re: Average by Group for Max QTR

sunny_talwar — Thu, 03 Nov 2016 13:53:44 GMT

I am glad I was able to help you learn a new trick