Limit line chart dimension by top 5 values

[#1]

Hi all

I am working with a line chart and would like to limit one of the dimensions to the top 5, ranked according to the most recent data for a second dimension on the x axis. Please see attached example - Destination is the dimension I would like to limit, Phase is on the x axis  and the y axis is the % of respondents who selected the Destination.

On the left hand chart I have used the  'Fixed number' limitation and set this to top  5 for the Destination dimension. This approach shows the top five data points determined separately for each Phase, which means I don't have a continuous series. For example, Japan is among the top 5 Destinations in Phase 4 and 5 but not Phases 1-3 and hence the data does not show for Phases 1-3. I would like to show the top 5 destinations ranked according to Phase 5, and then show the data for these five destinations for Phases 1-4 as well, to produce a continuous series. If Australia is not among the top 5, I also need to show Australia.

On the right hand chart I have tried a different approach by specifying Australia in the dimension field expression. How can I extend this to show the top 5 destinations in Phase 5 and Australia if it is not ranked among the top five?

Any help would be greatly appreciated.

Thank you

Emma

[sunny_talwar · Accepted solution]

May be this

If(Aggr(NODISTINCT Rank(count({<Q11_Selection={"Selected"}, Phase = {5}>}RESPID)), Q11_Destination) < 6,

Count({<Q11_Selection={"Selected"},Phase=>}  RESPID)/Count( {<Phase=>}distinct (RESPID)))

[#3]

Hi Sunny

Thanks again for your help, I should have realised that I could add an IF statement to the measure expression. Works perfectly and I also added an OR so that if Australia doesn't fall into the top 5 then it still appears on the chart:

If(Aggr(NODISTINCT Rank(count({<Q11_Selection={"Selected"}, Phase = {5}>}RESPID)), Q11_Destination) < 6 or Q11_Destination='Australia',

Count({<Q11_Selection={"Selected"},Phase=>}  RESPID)/Count( {<Phase=>}distinct (RESPID)))

Thanks so much

Emma

[#4]

Hi Sunny

Just on this chart again, do you know why when I use the Phase filter the line chart X axis updates? I need the line chart to always show Phase 1-5. I thought adding Phase=> to the below expression would achieve this, is there something else I need to add?

If(Aggr(NODISTINCT Rank(count({<Q11_Selection={"Selected"}, Phase = {5}>}RESPID)), Q11_Destination) < 6 or Q11_Destination='Australia',

Count({<Q11_Selection={"Selected"},Phase=>}  RESPID)/Count( {<Phase=>}distinct (RESPID)))

Thanks

Emma

[sunny_talwar]

May be try this

If(Only({<Phase>}Aggr(NODISTINCT Rank(count({<Q11_Selection={"Selected"}, Phase = {5}>}RESPID)), Q11_Destination)) < 6 or Only({<Phase>}Q11_Destination) = 'Australia',

Count({<Q11_Selection={"Selected"},Phase=>}  RESPID)/Count( {<Phase=>}distinct (RESPID)))