Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
rodolfoag
Contributor III
Contributor III

Rank a dynamic aggregator

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

1 Solution

Accepted Solutions
sunny_talwar

Try this

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

View solution in original post

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

Capture.PNG

rodolfoag
Contributor III
Contributor III
Author

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

rodolfoag
Contributor III
Contributor III
Author

Hi Sunny, my expectation is:

without selections:

   

Carl880Paris1
Edgard625Paris2
Lucy950Nice1

selecting all dates

   

Carl880Paris1
Edgard625Paris2
Lucy950Nice1

selecting 43101 and 43102:

   

SalesmanNamesum(SalesValue)FirstSortedValue(distinct Store,-Date)ranking
1595Manchester-
Carl450Manchester1
Edgard445Liverpool2
Lucy700Liverpool1
sunny_talwar

Try this

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

View solution in original post