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:
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.
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.