Group multiple dates

Gurus,

I have the below data set:

Ticket #	Date Created	Date Solved	Ticket Type	Open / Close Flag
1	1/26/2017		P1	Open
2	1/16/2017	1/21/2017	P1	Close
3	12/27/2016		P2	Open
4	12/22/2016	1/1/2017	P2	Close
5	12/19/2016	12/22/2016	P1	Close
6	11/19/2016	12/22/2016	P3	Close
7	11/18/2016	12/2/2016	P3	Close

And I am looking for attached chart( i just created a sample in excel)

Can you please help? Thanks.

Capture.JPG

Find more posts tagged with

Comments

KaLin

Can anyone help out user00328 with this request?

unknown

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:

http://knowledge.domo.com?cid=understandingjoins

Magic ETL videos to help get you started:

http://knowledge.domo.com?cid=advancedmagicetl

https://university.domo.com/label/prepare/sublabel/magic/vid/322955/

MattE

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

Multiple Date Sample.PNG

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.

Echelon

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.

[Deleted User]

@CaseyMorgan

Let me know if you have additional thoughts on the last 2 comments.

Thanks!

Caleb

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)

END

and you can use this to series. Hope this solves your question.

Echelon

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.

Echelon

@DaniBoy @CaseyMorgancould you please give us your thoughts on this? Or should I reach out to my Account Rep?

unknown

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.

Echelon

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.

user06244

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 me