Group multiple dates
I have the below data set:
|Ticket #||Date Created||Date Solved||Ticket Type||Open / Close Flag|
And I am looking for attached chart( i just created a sample in excel)
Can you please help? Thanks.
If you have two or more datasets that have a common date field (e.g. `Date Created`) you can join those input datasets onto the `Date Created` field using MagicETL as the easiest way to create that dataflow. You would want to group by the joined date field, in this case `Date Created`, and other fields that are relevant to the dataset.
Here is some additional information that might help:
Magic ETL videos to help get you started:
user00328- Did the answer provided help? I ask because I have a very similar scenario that I am trying to solve and I'm not seeing the connection with the first answer provided.
I also have rows with several different event date columns. I want to count how many types of each event happened in January, February, etc. in a grouped bar design. Very similar to your tickets, ours is an inventory record. Here is a smiplified version of the data and a sample visualization.
InventoryID Date purchased Date Received Date Sold cost price 101 1/5/2017 1/10/2017 2/21/2017 500.00 750.00 102 1/6/2017 1/8/2017 1/20/2017 300.00 420.00 103 1/8/2017 1/30/2017 2/8/2017 180.00 250.00 104 1/14/2017 2/1/2017 3/1/2017 700.00 990.00 105 1/21/2017 2/1/2017 3/4/2017 75.00 140.00 106 1/22/2017 2/14/2017 3/15/2017 120.00 190.00 107 1/24/2017 2/8/2017 3/6/2017 500.00 620.00 108 2/1/2017 2/10/2017 3/9/2017 450.00 700.00 109 2/5/2017 2/12/2017 2/20/2017 375.00 480.00 110 3/1/2017 3/10/2017 600.00 790.00
I can create three different cards with the correct results (one for Items purchased, one for Items recieved and one for Items Sold) but I am stumped with how to accomplish this all on one card so that the balance or imbalance of the activities can easily be seen. I can use one of the date fields to power the X Axis, but then the results of counting the other date columns don't measure what I want because they become "filtered" based on the X Axis date. All I can think of is that I must need a beast mode to create some type of date field or group to use as the X Axis, but everything I've tried to do has failed miserably.3
This sounds exactly like my problem, MattE. It seems there needs to be an over-arching calendar of some sort - I have different Sales Data that I need to display on one card, for performance in different metrics for a given month.0
Let me know if you have additional thoughts on the last 2 comments.
I think CASE function will solve this problem in Beast Mode.
For Example if the row matched to certain category, you add the amount other wise return 0.
Please look at following sample code.
CASE WHEN col1 = 'xxx' THEN sum(col_amount)
WHEN col1 = 'yyy' THEN sum(col_amount)
and you can use this to series. Hope this solves your question.0
I don't see how this solves anything Caleb. In Beast Mode, I can easily create formulas that would label or count if a Date Field equals say, the Current Date.
But there is no way to build on a card, and filter by "Current Date". There must be an ability to have an overarching Date Range Filter, than always forcing that filter to look at specific row level data.
I would call it, "Beast Mode Date Filter" - that way it would be self explanatory when Card Building.1
@DaniBoy@CaseyMorgancould you please give us your thoughts on this? Or should I reach out to my Account Rep?0
One additional thought on this, would be the importance of how you are accounting for 'NULL' values. If the 'Date Purchased' event is the trigger you are accounting for, then I would be certain to create a BeastMode that accounts for your 'NULL' values (e.g. CASE WHEN 'Date Purchased' IS NULL THEN 1 ELSE 0) and then apply that as a filter, so you pull out the NULL values.
You can then get a better understanding of how many events based on Date Purchased are triggered. You can follow the same logic for each Date event you are looking to analyze. You may also consider creating a series that accounts for each date activity (e.g. CASE WHEN 'Date Purchased' IS NOT NULL THEN 'Purchase' WHEN 'Date Received' IS NOT NULL THEN 'Received' WHEN 'Date Sold' IS NOT NULL THEN 'Sold'). As you create the series, and as you are filtering out null values, you will get a better look into the activities around multiple date columns in your dataset.
That is one way to approach this. We could get more specific, but that is just the thought of approaching this.1
This is NOT the same thing, but the NEXT best thing that I've been able to do, is through the ETL create separate datasets that pass a date field into a new field with a common name such as "Card Filter Date". Then, create a Page with separate Cards that look at each of the datasets that I want represented...
So a dataset for "Date Created", and then for "Date Solved", with Cards built to show the count per day.
THEN, use the Page Analyzer with the created field "Card Filter Date", so that you can manipulate each Card by that common date field.1
Im having this same issue.
We have a form submitted Date and then an acceptance date.
How did you get it to work with ETL its not making sense to me0
- 10.5K All Categories
- 3 Connect
- 913 Connectors
- 250 Workbench
- 458 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 182 Visualize
- 249 Beast Mode
- 2.1K Charting
- 11 Variables
- 77 Cards, Dashboards, Stories
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 168 Product Ideas
- 1.2K Ideas Exchange
- 9 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive