3 Replies Latest reply on Dec 6, 2016 8:02 PM by Kenneth Li

Sort the countries according to number of distinct value in a field

Hi,

I have created a table like below. What I wanted to do is for each country, how many columns >0 are available and sort in descending order.

e.g. Argentina 2 columns >0

Australia, 1 columns >0

Austria, 5 columns >0

i.e. Austria -> Argentina -> Australia .... I am wondering if there is a way to count based on the table and then I can directly sort that field afterwards.

Thanks.

Kenneth

• 1. Re: Sort the countries according to number of distinct value in a field

It's hard to say specifically without seeing your workbook, but you could create a calculated field which is effectively;

SUM (IF zn([FieldA]) > 0 THEN 1 ELSE 0 END + IF zn([FieldB]) > 0 THEN 1 ELSE 0 END + IF zn([FieldC]) > 0 THEN 1 ELSE 0 END)

Then sort your country dimension by this measure.

• 2. Re: Sort the countries according to number of distinct value in a field

calc:

SUM ( IF [whatever you are checking to be greater than zero] > 0 then 1 else 0 END )

Sort on that.

If [whatever you are checking to be greater than zero] is already an aggregate, then you have to do a WINDOW_SUM() instead of SUM().

And if you want to sort on a table calc, (which is what WINDOW_SUM is), then you can't simply specify that in the [Country] sort options.

If that's the case, maybe posting a sample workbook would help here. For me, it's easier to show you than to explain it.  If you post a workbook, specify your tableau version.

• 3. Re: Sort the countries according to number of distinct value in a field

Thanks Tom and Joe,

The window_sum way actually works. But I need to change the visualized table view so it can sum the number of unique code according to country. It's ok and thanks.

Also a related thread attach here

Cheers,

Kenneth