Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Set Analysis with Rank() Function

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

Tags (2)
1 Reply
MVP
MVP

Re: Set Analysis with Rank() Function

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)