Group multiple dates

Gurus,

 

I have the below data set:

Ticket #Date CreatedDate SolvedTicket TypeOpen / Close Flag
11/26/2017 P1Open
21/16/20171/21/2017P1Close
312/27/2016 P2Open
412/22/20161/1/2017P2Close
512/19/201612/22/2016P1Close
611/19/201612/22/2016P3Close
711/18/201612/2/2016P3Close

 

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

Can you please help? Thanks.

 

Comments

  • Can anyone help out user00328 with this request?

  • 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
    MattE Member

    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.

    InventoryIDDate purchasedDate ReceivedDate Soldcostprice
    1011/5/20171/10/20172/21/2017500.00750.00
    1021/6/20171/8/20171/20/2017300.00420.00
    1031/8/20171/30/20172/8/2017180.00250.00
    1041/14/20172/1/20173/1/2017700.00990.00
    1051/21/20172/1/20173/4/201775.00140.00
    1061/22/20172/14/20173/15/2017120.00190.00
    1071/24/20172/8/20173/6/2017500.00620.00
    1082/1/20172/10/20173/9/2017450.00700.00
    1092/5/20172/12/20172/20/2017375.00480.00
    1103/1/20173/10/2017 600.00790.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
    Echelon Contributor

    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.

  • @CaseyMorgan

     

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

    Thanks!

     

  • Caleb
    Caleb Member

    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
    Echelon Contributor

    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
    Echelon Contributor

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

  • 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
    Echelon Contributor

    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.

  • 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

This discussion has been closed.