topic Re: Using RANK to Define Chart Dimension? in QlikView

Using RANK to Define Chart Dimension?

Tue, 11 Sep 2012 09:29:13 GMT

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.

Re: Using RANK to Define Chart Dimension?

whiteline — Tue, 11 Sep 2012 09:33:05 GMT

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)

Re: Using RANK to Define Chart Dimension?

christophebrault — Tue, 11 Sep 2012 09:45:14 GMT

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

Re: Using RANK to Define Chart Dimension?

Tue, 11 Sep 2012 10:12:41 GMT

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.

Re: Using RANK to Define Chart Dimension?

christophebrault — Tue, 11 Sep 2012 10:17:25 GMT

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

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

Re: Using RANK to Define Chart Dimension?

whiteline — Tue, 11 Sep 2012 10:40:27 GMT

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.

Re: Using RANK to Define Chart Dimension?

Tue, 11 Sep 2012 15:09:22 GMT

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.