Change chart sort order based on variable value.
Hi,
I have created a variable that I am using on the y-axis of a bar chart. With the toggle letting switch between categorical and date values. I would like to be able to alternate how the y-axis is sorted based on which variable value is currently on the chart:
Date values: The date values are calculated by year and I would like the data sorted from the most recent to oldest year.
Categorical: Have the data sorted by total value in descending order.
When trying to sort the values manually in descending order everything looks fine except for date values which are then out of order based on the date values.
Is there a beast mode or setting that I can create that allows the sort order to dynamically change based on the data type of the variable?
Best Answer
-
You can utilize your variable and select the different fields depending on the value of your variable:
CASE WHEN `variable` = 'Date Value' THEN `date field` ELSE `category field` END
Toss that beast mode into your sort and select sort descending.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
You can utilize your variable and select the different fields depending on the value of your variable:
CASE WHEN `variable` = 'Date Value' THEN `date field` ELSE `category field` END
Toss that beast mode into your sort and select sort descending.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
I was able to create the custom sort using the following formula to accomodate the date field.
CASE
WHENVariable
= 'Date'
THEN MAX(CASE WHEN YEAR(Date
) = YEAR(CURDATE()) THEN 10
WHEN YEAR(CURDATE()) - YEAR(Date
) = 1 THEN 9
WHEN YEAR(CURDATE()) - YEAR(Date
) = 2 THEN 8
WHEN YEAR(CURDATE()) - YEAR(Date
) = 3 THEN 7
WHEN YEAR(CURDATE()) - YEAR(Date
) = 4 THEN 6
WHEN YEAR(CURDATE()) - YEAR(Date
) = 5 THEN 5
WHEN YEAR(CURDATE()) - YEAR(Date
) = 6 THEN 4
WHEN YEAR(CURDATE()) - YEAR(Date
) = 7 THEN 3
WHEN YEAR(CURDATE()) - YEAR(Date
) = 8 THEN 2
WHEN YEAR(CURDATE()) - YEAR(Date
) = 9 THEN 1
WHEN YEAR(CURDATE()) - YEAR(Date
) >= 10 THEN 0
END)
ELSE COUNT(Other Value
)
ENDUsing this variable I can leave the sort in descending order but when the variable is switched to date fields they are displayed in ascending order instead.
0 -
rather than all those individual WHEN statements, I just multiplied by -1 to invert the order. However my ELSE COUNT doesn't work. My value split out a ton into non unique values. Not sure why.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive