Sort by Last Column in card (heatmap)
I feel like this one is an easy one but I just can't think for the moment. I have a heatmap that shows data over time. I want to sort so that the highest number for the current (or last month of data) is descending from the top. If I do a normal sort it puts the first month in descending order, I just want it to be for the end first.
Also in order to make the months appear in the correct order I sort by the Date field first THEN by the calculated field that is coming up with this number.
Column on far right represents January 2018 and goes back 12 months grouped by month
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Best Answer
-
I'm not sure how to get access to redshift (we had access here when I started). I would contact Domo if you don't have it turned on.
Meanwhile, you can accomplish the same thing with a couple of steps inside a MySQL dataflow.
You would first need to create a list of Accounts and the max dates:
SELECT
`Name`
,MAX(`Date`) as `Max Date`
from table_1
GROUP BY `Name`
Then you would join this table to the full data set
SELECT
a.*
,b.`Max Date`
FROM table_1 a
LEFT JOIN table_2 b
ON a.`Name`=b.`Name`
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1
Answers
-
I would think I about creating a beastmode that would help you sort the data. Is the last column always the current month?
CASE WHEN `MonthField`=MONTH(CURDATE()) THEN `ValueField` ELSE 0 END
I think you should then be able to sort by this field in descending order first
I'm not really able to test this out without any of the data. If this doesn't work then could you supply some sample data so I can play around with it some?
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
You're kind for trying to help me test Scott.
It didn't work as I had it so I've prepared some test data for you that I made up.
It's the Heatmap card type with the following values:
- Category 1 = Date
- Category 2 = (Rejected % calculated field)
- Series = Name
- Sorting = Date Asc + Rejected % Desc (if I reverse them months sometimes move)
- Grouped by month
The calculation for Rejected % is
- SUM(`Rejected`) / (SUM(Accepted`)+SUM(Rejected))
For this example I only gave once piece of data per month so the SUM doesn't really do anything but it should be the same result.
Based on the test data I gave the goal is that the heatmap goes chronologically from left to right (per column) and that for the last month (always on the far right) the Rejection % be descending order from 100% to 0%.
Does that make sense? Again, thanks for taking the time here
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**0 -
Is the last month always 12?
I got it to sort correctly this way:
The =test beast mode is:
Otherwise, if your last month is not always 12 then you would need to add a field in the dataflow that would tell the formula what month to use. Something like `Max Month` that would be the same value for each line of data and then the beastmode would start with :
CASE WHEN MONTH(`Date`)=`Max Month` THEN...
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
OK thanks, I'll have to get back on how/if it worked(Monday). Leaving work shortly. 12 isn't always the last month, in the real one I have it as a rolling 12 months
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**0 -
Having some trouble figuring which ETL(s) to use to get the max date of the field I have. The calculator can't find a max date, just numerical calculations, date Operations can give me month of date, day of year, etc, but not max date.
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**0 -
I'm not sure if this is doable via an ETL. You can accomplish it with a redshift SQL dataflow.
Obviously you would want to partition by your unique ID rather than the NAME field, but I didn't have that in my data set. Let me know if this works for you. I can try to look into the ETL option, but I'm not sure if it's possible (yet)
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
I can look the specific syntax up my I only have access to MySQL DataFlows. Do you know if it has a comparable function? It doesn't like "Partition By". Does Redshift have to be enabled by support?
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**0 -
I'm not sure how to get access to redshift (we had access here when I started). I would contact Domo if you don't have it turned on.
Meanwhile, you can accomplish the same thing with a couple of steps inside a MySQL dataflow.
You would first need to create a list of Accounts and the max dates:
SELECT
`Name`
,MAX(`Date`) as `Max Date`
from table_1
GROUP BY `Name`
Then you would join this table to the full data set
SELECT
a.*
,b.`Max Date`
FROM table_1 a
LEFT JOIN table_2 b
ON a.`Name`=b.`Name`
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
Scott,
I think this will do it! Seems to be sorting correctly. Right now I have a lot of extra stuff going on because we're still iterating on this dataset and it's being pulled in from Excel still and not directly from the database yet which means I have to do ETL to correctly identify NULL fields, field types, etc so all my cards are based on that dataset which is then what this dataset uses as the input so it's Database->Excel->Domo->ETL->MySQL->Dataset. Once we get it connected I should be good with just Database->MySQL->Dataset.
The final beastmode ended up being
CASE WHEN MONTH(`Date`)=MONTH(`Max Date`)
THEN (SUM(`Rejected`) / (SUM(`Accepted`)+SUM(`Rejected`)))
ELSE 0
ENDHad to use the MONTH() function on Max Date as well
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive