I need to make a bar chart that combines multiple columns based on date

KristinDavis

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?

ArborRose

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.

MichelleH

@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.

ArborRose

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.

ArborRose

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.

MichelleH

@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.

ArborRose

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.