Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
mslottje
Not applicable

Rank function with multiple dimensions

Hi experts,

I've made a bar chart with my top 10 invoices over the last 4 years.

Because Qlik doesn't have a function that shows the total sum of these top 10 customers per year, I wanted to use a KPI object to show the total sum of the top 10 invoices in a certain year.

See the following screenshot (Years are ordered as 2014-2017).test.png

In the 4 different KPI objects I used the following function 4 times (Max Year, Max Year -1, -2, -3):

sum({1<Year={"$(=max({<year>}Year))"},[Invoice Number] = {"=rank(sum([Nett Amount new EUR]),4,1) <= 10"}>} [Nett Amount new EUR] )

The numbers shown in the KPI objects are based on the top 10 invoices over the 4 years, instead of the top 10 invoices per year.

Could you guys help me out with this one or give me another solution?

Thanks a lot in advanceSmiley Happy

Mike

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Rank function with multiple dimensions

I removed the double quotes within the Max({<year>}Year) thing... can you check... also do you have two year fields? Year and year? QlikView is case sensitive, make sure to use the correct casing everywhere

Sum({1<Year={"$(=Max({<Year>} Year))"}, [Invoice Number] = {"=Rank(Sum({1<Year={$(=Max({<Year>} Year))}>} [Nett Amount new EUR]), 4, 1) <= 10"}>} [Nett Amount new EUR])

6 Replies
sunny_talwar
Not applicable

Re: Rank function with multiple dimensions

May be this

Sum({1<Year={"$(=Max({<year>} Year))"}, [Invoice Number] = {"=Rank(Sum({1<Year={$(=Max({<year>} Year))}>} [Nett Amount new EUR]), 4, 1) <= 10"}>} [Nett Amount new EUR])

mslottje
Not applicable

Re: Rank function with multiple dimensions

Thanks for your quick reply Sunny, but this function is showing a NULL value " - "..

sunny_talwar
Not applicable

Re: Rank function with multiple dimensions

I removed the double quotes within the Max({<year>}Year) thing... can you check... also do you have two year fields? Year and year? QlikView is case sensitive, make sure to use the correct casing everywhere

Sum({1<Year={"$(=Max({<Year>} Year))"}, [Invoice Number] = {"=Rank(Sum({1<Year={$(=Max({<Year>} Year))}>} [Nett Amount new EUR]), 4, 1) <= 10"}>} [Nett Amount new EUR])

mslottje
Not applicable

Re: Rank function with multiple dimensions

This did the job! Thanks a lot Sunny, you helped me a lot!Smiley Happy

OmarBenSalem
Not applicable

Re: Rank function with multiple dimensions

Hi Sunny,

Can you please explain to me what does the 4 and 1 do?

Rank(Sum({1<Year={$(=Max({<Year>} Year))}>} [Nett Amount new EUR]), 4, 1) <= 10


Thanks !

sunny_talwar
Not applicable

Re: Rank function with multiple dimensions