Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
smiling_cheetah
Contributor

Ranking customers both on the current and previous years in one table (and restrict to 10 rows)

Hi everyone, 

I hope the title is not too confusing, but basically I have a table looking like following: 

Table:
[date, sales volume, customer
01.01.1970, 100, A
01.01.1970, 200, B
...
31.12.1970, 500, C
31.12.1970, 550, D
...
01.01.1971, 120, A
01.01.1971, 230, B
...
31.12.1971, 550, C
31.12.1971, 650, D
]

What I need to do is to have table which basically shows 10 clients with biggest sum([sales volume]) this year,

and shows those clients' ranks in the previous year.

 

I do understand how I rank current year (with set analysis), but can't get my head around how to rank the previous year's sales in the same table

Any help would be really appreciated,

Thanks in advance 

Labels (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Ranking customers both on the current and previous years in one table (and restrict to 10 rows)

[ TIP:  to show any contributor that you like and appreciate their answers please CLICK the like icon (thumbs up) ]

The three measures are:

If( Rank( Sum({<Year={2018}>}Sales))<=10 , Sum({<Year={2018}>}Sales) )

If( Rank( Sum({<Year={2018}>}Sales))<=10 , Rank( Sum({<Year={2018}>} Sales)) )

If( Rank( Sum({<Year={2018}>}Sales))<=10 , Rank( Sum({<Year={2017}>} Sales)) )

Merknad 2019-07-21 224949.png

 

2 Replies
MVP
MVP

Re: Ranking customers both on the current and previous years in one table (and restrict to 10 rows)

[ TIP:  to show any contributor that you like and appreciate their answers please CLICK the like icon (thumbs up) ]

The three measures are:

If( Rank( Sum({<Year={2018}>}Sales))<=10 , Sum({<Year={2018}>}Sales) )

If( Rank( Sum({<Year={2018}>}Sales))<=10 , Rank( Sum({<Year={2018}>} Sales)) )

If( Rank( Sum({<Year={2018}>}Sales))<=10 , Rank( Sum({<Year={2017}>} Sales)) )

Merknad 2019-07-21 224949.png

 

smiling_cheetah
Contributor

Re: Ranking customers both on the current and previous years in one table (and restrict to 10 rows)

Hi, thanks a lot! 

So the idea is to use IF statement instead of set analysis