4 Replies Latest reply: Apr 13, 2017 4:55 AM by Sunny Talwar RSS

    Limit line chart dimension by top 5 values

    Emma Aitken

      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

        • Re: Limit line chart dimension by top 5 values
          Sunny Talwar

          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)))

           

           

          Capture.PNG

            • Re: Limit line chart dimension by top 5 values
              Emma Aitken

              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

              • Re: Limit line chart dimension by top 5 values
                Emma Aitken

                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