Average by Group for Max QTR

[vinieme12]

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

[sunny_talwar]

May be this:

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

[vinieme12]

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

[sunny_talwar · Accepted solution]

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))

[vinieme12]

Fab , thanks a lot stalwar1‌

[sunny_talwar]

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

[jyothish8807]

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,

KC

[sunny_talwar]

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.

[jyothish8807]

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)".

[sunny_talwar]

Exactly