Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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
Honored Contributor II

Re: Using RANK to Define Chart Dimension?

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)

6 Replies
whiteline
Honored Contributor II

Re: Using RANK to Define Chart Dimension?

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
Valued Contributor

Re: Using RANK to Define Chart Dimension?

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

Not applicable

Re: Using RANK to Define Chart Dimension?

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
Valued Contributor

Re: Using RANK to Define Chart Dimension?

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

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

whiteline
Honored Contributor II

Re: Using RANK to Define Chart Dimension?

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

Re: Using RANK to Define Chart Dimension?

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.

Community Browser