Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
danielle_v
Contributor

Rank in Staright Table

Hi All,

I have put together the below straight table to show volume by country.

VOLUME2.PNG

The Volume expression is  sum({$<Year={$(vCurrentYear)}, Month={"<=$(=max(Month))"}>} Volume)

The rank expression is rank(sum({$<Year={$(vCurrentYear)}, Month={"<=$(=max(Month))"}>} Volume))

 

I've clearly done something wrong, but I haven't yet been able to figure it out! I'd like the rank to show 1 for China, 2 for Italy, 3 for Bangladesh etc.

 

Any help would be greatly appreciated!

1 Solution

Accepted Solutions

Re: Rank in Staright Table

Can you try this

Only({$<Year={$(vCurrentYear)}, Month={"<=$(=max(Month))"}>}
Aggr(
Rank(Sum({$<Year = {$(vCurrentYear)}, Month = {"<=$(=max(Month))"}>} Volume))
, Country)
)
6 Replies
tincholiver
Contributor II

Re: Rank in Staright Table

Can you share the app?
the expresion looks good, it shoudn´t to show this result
marwen_garwachi
Contributor II

Re: Rank in Staright Table

Hi, 

try this : 

aggr(rank(sum({$<Year={$(vCurrentYear)}, Month={"<=$(=max(Month))"}>} Volume)),Country)

 

Marwen

danielle_v
Contributor

Re: Rank in Staright Table

Hi Both,

Thanks for your responses. Unfortiunately I'm not able to share the app.

Marwen - I have implemented your expression and it's looking a lot closer now, as per the below screenshot. I've amended the background data slightly which is why the figures don't quite match my original screenshot, but the rankings almost seem to be working. The only thing not quite right is that the rankings seem to be null for any countries where there is no volume shipped in the selected month (the volumes/rankings are based on year to date based on selected year and month, as per my original post). I'll try and figure this out now, unless anyone has any pointers?

VOLUME3.PNG

 

Thanks again!

marwen_garwachi
Contributor II

Re: Rank in Staright Table

Maybe you can try o supress null values (on dimension tab) to hide countries with no volume shipped ?

Capture.JPG

or supress zéro values (on presentation tab) : 

 


Capture2.JPG

 

Marwen

Re: Rank in Staright Table

Can you try this

Only({$<Year={$(vCurrentYear)}, Month={"<=$(=max(Month))"}>}
Aggr(
Rank(Sum({$<Year = {$(vCurrentYear)}, Month = {"<=$(=max(Month))"}>} Volume))
, Country)
)
danielle_v
Contributor

Re: Rank in Staright Table

Thanks Sunny - this solution works perfectly!

Thanks too to everyone else for your help and responses