9 Replies Latest reply on Jul 12, 2018 2:27 AM by Mahfooj Khan

Sorting table by calculated field

Sheetal Panchal Jun 13, 2017 9:09 PM

Hi,

I have a table with 5 dimension columns (Month, States, City, Company, Department) and 4 calculated field columns (Spending 2016, Spending 2017, Variance, Variance %). I want to sort the table by Variance %, but it just doesn't work. There is hierarchy between State and Cities and between Company and Department. Also, I have highlighted the Variance % cells in red where the Variance % is more than 10% or less than -10%.

I have tried following approaches so far:

1. Sorted only the Variance % column in descending order

2. Sorted all the 5 dimension columns by Variance % - Custom.

3. Added a rank column which uses formula Rank(Variance %). The ranking seems correct but when sorted using this column, it doesn't sort the table.

Can someone help, please!

1. Re: Sorting table by calculated field

Zhouyi Zhang Jun 13, 2017 9:14 PM (in response to Sheetal Panchal)

Hi, Sheetal

When you say sort by variance %, you mean the hierarchy between state and cities and company/department can be broken, is it?

And If yes, could you provide a sample workbook?

ZZ
Like Show 0 Likes(0)

Actions
2. Re: Sorting table by calculated field

Mahfooj Khan Jun 14, 2017 12:08 AM (in response to Sheetal Panchal)
Hi Sheetal,

Find my approach, You can drag your variance% discrete measure before your dimension and edit this measure with negative sign . -ve Sign will sort your measure. I've used with hierarchy and its perfectly sorting without breaking the sort order in hierarchy. Give a try an let us know if this works in your case.
workbook v10.0 attached for your reference.

Mahfooj

sample v10.twbx 228.2 KB

1 of 1 people found this helpful
Like Show 2 Likes(2)

Actions
3. Re: Sorting table by calculated field

Sheetal Panchal Jun 14, 2017 12:31 AM (in response to Zhouyi Zhang)
Hi ZZ,

Thanks for your reply. Actually there is no direct hierarchy. It just falls under same group. I have attached a sample version. This is similar to what I am doing. Only in my actual version, I have highlighted the Variance % in red where the % is more than 10%. I don't think that would be a blocker for sorting. Can you please help to sort the table in the attached based on the Variance %? Also, the column headers for the columns on the left disappear as I add more columns. Can you help how to adjust that?

Thanks,
Sheetal.

Sorting_Test.twbx 26.8 KB
Like Show 0 Likes(0)

Actions
4. Re: Sorting table by calculated field

Sheetal Panchal Jun 14, 2017 12:41 AM (in response to Mahfooj Khan)
Hi Mahfooj,

Thank you so much for your reply. I have tried the approach you mentioned but it didn't work. It adds negative sign in front of the Variance %. Also I would like to keep the order of the columns as is - Variance % column at the end. Regarding, the hierarchy, actually there is no direct hierarchy. It just falls under same group. I have attached a sample version. This is similar to what I am doing. Only in my actual version, I have highlighted the Variance % in red where the % is more than 10%. I don't think that would be a blocker for sorting. Can you please help to sort the table in the attached based on the Variance %? Also, the column headers for the columns on the left disappear as I add more columns. Can you help how to adjust that?

Thanks,
Sheetal.

Sorting_Test.twbx 26.8 KB
Like Show 0 Likes(0)

Actions
5. Re: Sorting table by calculated field

Zhouyi Zhang Jun 14, 2017 12:58 AM (in response to Sheetal Panchal)
Hi, Sheetal

If you don't have the hierarchy, that will be easy.

You need move the period, state right to city as show below

and then sort city by variance % with desc

Workbook attached for your reference. Hope this could help and if any question, please let me know

ZZ

Sorting_Test.twbx 22.4 KB
Like Show 0 Likes(0)

Actions
6. Re: Sorting table by calculated field

Mahfooj Khan Jun 14, 2017 5:29 AM (in response to Sheetal Panchal)
Hi Sheetal,

Thanks for the explanation. Workbook attached for your reference. Have a look
Let us know if you've any query.

Mahfooj

Sorting_Test.twbx 21.7 KB
Like Show 0 Likes(0)

Actions
7. Re: Sorting table by calculated field

Sheetal Panchal Jun 19, 2017 2:20 PM (in response to Mahfooj Khan)

Thank you so much Mahfooj! Sorry for late reply. I was having some issues with my login. Can you also tell me how to space out the column names in the table. My column data is displayed by separated comma, however only one column header is displayed for 3 columns. How can I space out the columns so all the column headers can be seen?
Like Show 0 Likes(0)

Actions
8. Re: Sorting table by calculated field

Sheetal Panchal Jun 19, 2017 2:21 PM (in response to Zhouyi Zhang)

Thank you so much Zhouyi. This worked in my test dashboard but not the original dashboard. Maybe because of structure of the data. Thanks for your help!
Like Show 0 Likes(0)

Actions
9. Re: Sorting table by calculated field

Mahfooj Khan Jul 12, 2018 2:27 AM (in response to Sheetal Panchal)

Go to Analysis menu tab> Table Layout>Advanced...
And increase the fields labels

Hope this may help.

Mahfooj
Like Show 0 Likes(0)

Actions