7 Replies Latest reply on Oct 27, 2018 12:58 PM by Michel Caissie

    Conditional Grouping of Dimension as per rank

    Adil Jamal

      Hey Community,

      I've been struggling with the conditional grouping of one of the dimension for past couple of days.

      I have the fuel consumption data of a particular country state wise and industry wise for a span of 10 years.

      I wish to prepare a year wise Sankey diagram ( put year in 'pages') in which the left side I've my fuel category ( since they're only 5, I'll take all of them) and on the right side, it has industries (since they're more than 10, I want to select the top 5 industries as per their energy consumption and put rest of the industries under 'others' section.)

      I know how to prepare Sankey, I need help in the above bold part.

      i.e., how to select the top 5 industries as per their consumption and put the rest of the industries under the 'others' section.

       

      I prepared a Sankey but I'd manually selected rather than doing the conditional grouping of the industries to be considered in top 5 and put rest in the 'others' section. I've attached the screenshot of that.

        • 1. Re: Conditional Grouping of Dimension as per rank
          Michel Caissie

          Adil,

           

          You can create a dimension with only 6 values (5 dynamic values according to their rank  and 1 other  regrouping all other sectors)

          using only lod.

          Using table calculations like rank would imply to have Industry Sector in the detail and it would be problematic to keep a single mark for others, and I am not

          even sure it would be feasible  in a Sankey.

           

          But if you only want the top 5 (and not top X  where x is a parameter with value from whatever to whatever) , you can do this.

           

          First compute  [Energy Consumed (by Sector)] with

          {FIXED [Industry Sector]: SUM( [Energy Consumed] )}

           

          Next you can easily compute a  isTop1 boolean  with

          [Energy Consumed (by Sector)] = {MAX([Energy Consumed (by Sector)])}

           

          isTop2 with

          [Energy Consumed (by Sector)] =  {MAX( if not [isTop1] then [Energy Consumed (by Sector)] end)}

           

          isTop3 with

          [Energy Consumed (by Sector)] =  {MAX( if not [isTop1] and not [isTop2]  then [Energy Consumed (by Sector)] end)}

          etc,...

           

          and finally the new Industry sector top5  dimension  with

          if [isTop1] or [isTop2] or [isTop3] or [isTop4] or [isTop5] then  [Industry Sector]  else 'others' end

           

           

          As you will see I also added some stuff to sort the values, but always keeping 'others' at the bottom whatever it's value.

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: Conditional Grouping of Dimension as per rank
            Adil Jamal

            Hey Michel,

            Thanks for the help. I was in dire need.

            Also, could you please tell me how to select top 'X' via Parameter method which you talked earlier.

            Much Thanks!!

            • 3. Re: Conditional Grouping of Dimension as per rank
              Michel Caissie

              Adil,

               

              It would be the same mechanic, but you would need a bunch of calculations.

               

              You would need a isTopX  calculation  for  every selectable value of X

              Same thing for  The IndustrySectorTopX

               

              And the actual dimension would be

               

              case  [parameter]

              when 1 then  IndustrySectorTop1

              when 2 then  IndustrySectorTop2

              when 3 then  IndustrySectorTop3

              etc,...  1 line for every selectable X

              end

              1 of 1 people found this helpful
              • 4. Re: Conditional Grouping of Dimension as per rank
                Adil Jamal

                Thanks again Michel.

                I have got a slight doubt that though. The first method worked just fine.

                In the ' Top X via Parameter' I still want the rest of my industries to be in 'other'. i.e., If I select the top 7 via parameter, then it should show 7 industries and 1 'other' which will group the rest of the industries.

                What I understood is, the calculation you posted above 'case [parameter]' doesn't take into consideration the 'other' grouping, unlike your first solution.

                How can I resolve that?

                • 5. Re: Conditional Grouping of Dimension as per rank
                  Michel Caissie

                  if the x = 7  then the case should return the  Industry Sector top7 calculation   which should contain   an   else 'others'

                   

                  if [isTop1] or [isTop2] or [isTop3] or [isTop4] or [isTop5] or [isTop6] or [isTop7] then  [Industry Sector]  else 'others' end

                  1 of 1 people found this helpful
                  • 6. Re: Conditional Grouping of Dimension as per rank
                    Adil Jamal

                    Thanks again, man

                    You're a Savior.

                    Could you please look at this problem as well?

                    Filling the Gap in the Sankey Diagram

                    Unable to understand the presence of the gap. The viz looks awfully disturbed

                    • 7. Re: Conditional Grouping of Dimension as per rank
                      Michel Caissie

                      Sorry Adil, but I am really not familiar with Sankey .

                       

                      And I think Tableau have a max size for a line.  You can test this using a single parameter on the size  and you will see that when the parameter is equal to the max value of the axis the size is around  20% of the axis max value,  and it doesn't grow in size if the parameter gets higher values. So I am not sure if it is possible to fill those gaps.