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
Example.qvw 136.8 K
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).
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.
FillingRates.gif 6.6 K
aggr calculates your expression:
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.
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.