Rank as column in pivot table

[wayne-a]

Hi I'm trying to display the rank calculated with a chart function as the columns in a pivot table but can't get this to work.  My data looks like this.  Each visit has an incrementing ID value so I can rank them per person to get a relative visit number.  

And I'd like the pivot table to look like this:

If I do the rank as a select from the database as it's own column in the load, this works.  But I need to be able to allow the user to select different date ranges so I need the rank to be calculated as the filtering is applied so the visit numbers always start at 1.

I've tried using Rank(VisitID) and although it says "ok" in the expression editor, results in an invalid dimension.

I saw an example using an aggregate and tried Aggr(Rank(VisitID),User) but that shows only 1 visit per person like this:

Thanks in advance for any ideas and help.

[Oleg_Troyansky · Accepted solution]

Hi Wayne,

You are pretty close, I think. If you want to rank visits by User, your aggregation should include VisitID as one of the dimensions, like this:

Aggr(Rank(VisitID),User, VisitID)

To be sure that visits are sorted correctly, you may want to sort your visits within the AGGR:

Aggr(Rank(VisitID), User, (VisitID (NUMERIC, ASC)) )

Allow me to invite you to my sessions on Set Analysis and AGGR at the Masters Summit for Qlik (coming up to Hamburg soon) or to my Qlik Expert Class (coming up to Vienna soon). See links in my signature.

Cheers,

Oleg Troyansky

[wayne-a]

Oleg, Thank you very much!  That was it!