Rank a dynamic aggregator

[Anonymous]

Hi Qlik experts,

I have a requirement to rank a salesman within a store, the issue I can't handle is due to this store being an expression, because the salesman can be in a different store on each day.

What I need is after  select ing a date, I want to see the last store this salesman worked (I got that using firstsortedvalue) and the rank of this salesman in this store. I tried to use rank (aggr(...)  but I got  "nested aggreagation not allowed".

The app follows attached, the left chart is with the expression commented.

thanks in advance

RS

[sunny_talwar]

Rank in the store across all dates or just the selected date? What is the expected output you hope to see here?

May be one of these

1) Rank(Sum(SalesValue))

2) Aggr(Rank(Sum(SalesValue)), Store, SalesmanName)

[Anonymous]

Hi Sunny,

thanks again for replying.

I had this solution when the store were fixed, but now with the dynamic store I need rank the salesmen based on the last store where they were allocated, if we got no filters , with this solution, we'll have null. (sorry I didn't specified that on the thread).

[sunny_talwar]

I am not sure I understand. Would you be able to provide the exact numbers you are hoping to get with and without selection

[Anonymous]

Hi Sunny, my expectation is:

without selections:

Carl	880	Paris	1
Edgard	625	Paris	2
Lucy	950	Nice	1

selecting all dates

Carl	880	Paris	1
Edgard	625	Paris	2
Lucy	950	Nice	1

selecting 43101 and 43102:

SalesmanName	sum(SalesValue)	FirstSortedValue(distinct Store,-Date)	ranking
	1595	Manchester	-
Carl	450	Manchester	1
Edgard	445	Liverpool	2
Lucy	700	Liverpool	1

[sunny_talwar · Accepted solution]

Try this

Aggr(If(FirstSortedValue(TOTAL <SalesmanName> distinct Store,-Date) = Store, Rank(Sum(SalesValue))), Store, Date, SalesmanName)