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

# Conditional Grouping of Dimension as per rank

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

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

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

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

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

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

Thanks again, man

You're a Savior.

Could you please look at this problem as well?

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

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

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.