I need to make a bar chart that combines multiple columns based on date
Simply put, I want to display how many users completed each program in Q4 2023, and I want to later be able to select a different date (so later, for example, I can see how many people completed programs in Q1 2024). I want my users to see the most popular progams during a given time period.
I think a bar graph will display that, however I can't figure out how to get my data into that format, and I think it will require a Beast mode, since I will be changing the date by which I want to see program completion.
The problem? The dates for each program are in different columns, since a user can complete more than one program, like this:
Anyone have an idea of how to solve this?
Best Answers
-
If you put your data in a format like the webform below. You can use it as a source on a Domo card and let the card do the aggregation (finding the number of per whatever)
In this case, I'm showing months and year. Just add a function for quarter where the formula is quarter([date]) and add it to the columns.
Note, the output will "break" on anything you add to the columns. If we take off the monthname and remove month from the sort, you will get the totals by year.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **1 -
@KristinDavis I agree with @ArborRose about the format of the data. To get it into that format, I suggest using a Dynamic Unpivot tile in MagicETL to add Program as its own column. Then you should be able to create a card off of that output dataset with Date in your X axis, count/distinct count of user in the Y axis, and Program as the series.
1
Answers
-
Typically you want your data in detail form such as:
user program date
———- ————— ——————-
User A program2 2024-11-15
User B program2 2024-10-20
User B program3 2024-10-25
User D program1 2023-03-01
User D program3 2024-12-01
User D program4 2024-11-01
User E null null
Then build out your card, whether that be a table chart, bar chart, etc.
I often include functions for Year, Month by using formulas such as
YEAR: YEAR(date)
Month: Month(date)
Monthname: Monthname(date)
Month gives an integer for sorting. Monthname gives the month name as January, February, etc.
Then you can aggregate on the card. Aggregation is grouping to get the COUNT, SUM, AVG, etc. I will put a demo together and provide a screenshot here on the post.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
If you put your data in a format like the webform below. You can use it as a source on a Domo card and let the card do the aggregation (finding the number of per whatever)
In this case, I'm showing months and year. Just add a function for quarter where the formula is quarter([date]) and add it to the columns.
Note, the output will "break" on anything you add to the columns. If we take off the monthname and remove month from the sort, you will get the totals by year.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **1 -
@KristinDavis I agree with @ArborRose about the format of the data. To get it into that format, I suggest using a Dynamic Unpivot tile in MagicETL to add Program as its own column. Then you should be able to create a card off of that output dataset with Date in your X axis, count/distinct count of user in the Y axis, and Program as the series.
1 -
And you could create a bar chart such as…
quartername:
CASE
WHEN QUARTER(date
) = 1 then 'Quarter1'
WHEN QUARTER(date
) = 2 then 'Quarter2'
WHEN QUARTER(date
) = 3 then 'Quarter3'
WHEN QUARTER(date
) = 4 then 'Quarter4'
END
Expand on the chart by setting filters with your selection of year. Or include year as part of the chart. You can make a dropdown for year by making a second card, and creating a filter chart type - dropdown. Or simply drop your year formula onto the filters and pick which year you want.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive