topic Re: Rank a dynamic aggregator in QlikView

Rank a dynamic aggregator

Anonymous — Thu, 08 Feb 2018 10:41:02 GMT

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

Re: Rank a dynamic aggregator

sunny_talwar — Thu, 08 Feb 2018 13:20:57 GMT

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)

Re: Rank a dynamic aggregator

Anonymous — Thu, 08 Feb 2018 13:56:29 GMT

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

Re: Rank a dynamic aggregator

sunny_talwar — Thu, 08 Feb 2018 14:09:33 GMT

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

Re: Rank a dynamic aggregator

Anonymous — Thu, 08 Feb 2018 14:15:24 GMT

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

Re: Rank a dynamic aggregator

sunny_talwar — Thu, 08 Feb 2018 14:27:02 GMT

Try this

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