Best Way to Represent Dates as Columns in Table
First time I've come across a requirement like this. The goal is to see each month and have a calculation of the variance %increase or %decrease for that month and highlight them when it's a certain amount. The table view isn't necessarilly a requirement but the series contains over 200 records so i'm finding it hard to represent that in anything but a table, but even with the table i can't figure out the best way to get the dates across the top by month. Another problem this introduces is color rules. If I want to highlight any value over say +- 50% I can't because you choose a field, and choose color logic. I would have to have as many color rules as I do months of data (since each date might be its own column grouped monthly).
I tried experimenting with uncollapse columns but I don't want to have to define dates until the end of time as the value for a new column. Thoughts? See the attached small excerpt to see how it looks in Excel. Again, the "Account 1,2 3" is currently over 200 unique ones
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Comments
-
Have you tried using a Sumo card? That is really the best way to pivot data in a visualization right now.
I ran into this issue in the opposite direction. I had a data set with the months listed as separate columns and I needed a flat file.
I was able to find a dynamic SQL statement to collapse the columns regardless of how many dates there were. Dynamic SQL is outside of my comfort zone, but I will share the code with you here to see if you can use it to help you "pivot" or uncollapse your data.
SELECT
GROUP_CONCAT(CONCAT('Select `Product Type`,',
QUOTE(COLUMN_NAME),
' AS `Date`, ',
'`',
COLUMN_NAME,
'` as `Revenue`',
'FROM original')
SEPARATOR ' UNION ALL ') AS product_info
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'original'
AND COLUMN_NAME NOT IN ('Product Type');
CREATE PROCEDURE transpose()BEGIN
SELECT product_info into @sql2 FROM for_transpose;
SET @str=concat('create table products as ',@sql2);
PREPARE q from @str;
EXECUTE q;END
call transpose;
SELECT
`Product Type`,
STR_TO_DATE(REPLACE(`Date`, '/', ' '), '%m %d %Y') AS `Date`,
ROUND(`Revenue`, 2) AS `Revenue`
FROM
products
ORDER BY `Product Type` , `Date`These steps essentially do the opposite of what you are looking for, but perhaps they will help you get to your answer.
Good luck
______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________2 -
Did the reply from @ST_-Superman-_ help? If so please mark it solved by clicking on the "Accept as Solution" button.
Thanks!
Dani aka "Mr.Dojo"
Dojo Admin
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
**You can update your Dojo Community name and avatar by clicking on your avatar then the "My Profile" button.0
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 38 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 258 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 104 Apps
- 714 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 26 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 653 ひらめき共有