Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using RANK to Define Chart Dimension?

Hiya Guys,

I would like to show a Chart containing the top-ten Courses based on their Revenue values - however the data in the Chart will not contain Revenue data.  An added complication is that, even though all Courses have a Revenue value - not all Courses have a "Filling Rate" percentage - and I would like to show the top-ten Courses by Revenue who have a "Filling Rate" percentage.

I have tried many different methods to achieve this - and the closest I have come is to dynamically calculate each Course's "RANK", and if it is in the top-ten Courses who meet the criteria, then flag as "TRUE" (1).  My "RANK" formula (uvFillingRate_TopTen - which works using a Straight Table) is as follows:

If(Rank(Num($(uvRevenue)) * Num(If(Not IsNull($(uvFillingRate)), 1, 0))) <= 10, 1, 0)

This formula produces the following (correct) result:

Course Name

Rev (K) 2012

Filling Rate

TopTen

###

###%

0

Course A

###

###%

1

Course B

###

###%

1

Course C

###

0

Course D

###

0

Course E

###

0

Course F

###

###%

1

Course G

###

###%

1

Course H

###

###%

1

Course I

###

###%

1

Course J

###

###%

1

Course K

###

###%

1

Course L

###

###%

1

Course M

###

###%

1

Course N

###

###%

0

Course O

###

###%

0

Course P

###

###%

0

Course Q

###

###%

0

Course R

###

###%

0

Course S

###

###%

0

* Values hidden.

However, when I attempt to use this formula within a Calculated Dimension for a Chart, ie:

=If($(uvFillingRate_TopTen)=1, CourseType_Name)

I get an "// Error in calculated dimension" error.

Could this be due to the order in which QlikView evaluates variables? - or the RANK function?

Is there a better/cleaner way to achieve my desired result?

Any help would be greatly appreciated.

Cheers,

Steve.

1 Solution

Accepted Solutions
whiteline
Master II
Master II

Hi.

to use this as calculated dimension you should tell QV how to calculate it (the dimensions).

Just add aggr() function with desiered dimensions, for example:

=Aggr(If($(uvFillingRate_TopTen)=1, CourseType_Name), CourseType_Name)

View solution in original post

6 Replies
whiteline
Master II
Master II

Hi.

to use this as calculated dimension you should tell QV how to calculate it (the dimensions).

Just add aggr() function with desiered dimensions, for example:

=Aggr(If($(uvFillingRate_TopTen)=1, CourseType_Name), CourseType_Name)

christophebrault
Specialist
Specialist

Hi,

See the attached file.

I've loaded your data without the column TopTen because you mustn't calculate it in the script if you want to make selections then.

Create a new chart with Course Name as a dimension

Create two expression : 1 sum(Rev (K) 2012) but desactivate it with the check box just on the right

                                            2 sum(Filling Rate)

Go to dimensionals limits an restrict to max 10 values with the first expressions

The chart will use revenue to filter the max(10) dimensions and show their filling rate.

Hope this works for you

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Not applicable
Author

Thanks whiteline and christopherbrault,

whiteline:  Unfortunately this hasn't worked - I was very hopeful that it would be that simple (I don't quite understand what the "Aggr" function does yet - so at the moment I cannot debug your expression.  It is close, but unfortunately it gives the following result (see attached).

Instead of Courses A, B, F, G, H, I, J, K, L, M (the desired top-ten Couses in descending Revenue order) it gives:

A, B, F, G, H, K, L, Null     (missing I and J, including a Null, and also the data is incorrect - see attached - there are no values for H and K, for example).

Any ideas?!

christopherbrault:  Unfortunately I think I have tried your suggestion - however it will give only the top-ten Couses by Revenue... it will not take into account those without a "Filling Rate" percentage.  Please correct me if I am wrong.

Cheers,

Steve.

christophebrault
Specialist
Specialist

I didn't notice that filling rate could be null.

try if([Filling Rate]>0,sum([Rev (K) 2012])) in the first expression

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
whiteline
Master II
Master II

Hi,

aggr calculates your expression:

If($(uvFillingRate_TopTen)=1, CourseType_Name)

for each CourseType_Name.

It's like a straight-table with CourseType_Name as dimension (and expression that shows CourseType_Name only for thouse that's in Top Ten).

Considering the above, you have to modify your TopTen check so that it works in such straight table.

And Nulls, to hide them check 'supress when value is null' for dimension.

Not applicable
Author

Thanks whiteline,

This was the correct answer - it was my understanding of the underlying data which meant I was not seeing what I expected.  Also, thank you for your explanation of "Aggr" - I have read into this further and understand it a lot more... this is a very powerful function which I will now, no doubt, use in abundance!  🙂

christopherbrault:  Unfortunately I was not able to get your suggestion to give me the desired result.

Cheers guys,

Steve.