Set Analysis with Rank() Function

Fri, 03 May 2013 20:13:27 GMT

Hello,

I have a straight table which has a single row per location, and then obviously some metrics per each location. I am trying to add a Rank column which ranks each location against a certain metric. If I create an expression as below;

=Aggr(Rank(Sum({1}[TY Net Sales Dollars])),Location)

This works great, and ensures that even if I make a selection which limits the number of locations visible in the straight table, the rank is still the rank across all locations.

The problem is, I don't want to rank on just TY Net Sales, I want to rank on YTD Change TY vs LY, for which I have an expression working correctly;

=(sum({$<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [TY Net Sales Dollars])

/ sum({$<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [LY Net Sales Dollars])) - 1

It is my understanding that this {1} in the first expression is what causes it to always rank across all locations, so my question is how can I keep this in my rank expression, while using the calculation in my second expression instead just "TY Net Sales Dollars"?

Thanks!

Chris

Re: Set Analysis with Rank() Function

swuehl — Fri, 03 May 2013 22:43:31 GMT

If I understood correctly, just replace the set identifier $ (i.e. the current selection set) with 1 (the full record set):

=(sum({1<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [TY Net Sales Dollars])

/ sum({1<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [LY Net Sales Dollars])) - 1

-->

=Aggr(Rank(

(sum({1<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [TY Net Sales Dollars])

/ sum({1<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [LY Net Sales Dollars])) - 1

),Location)

topic Set Analysis with Rank() Function in QlikView

Set Analysis with Rank() Function

Re: Set Analysis with Rank() Function