Create beast mode dates

Hi everyone, help needed! Here is how my table looks below. What I want to show in Analyzer is the count of accounts of these services over time. My problem is how the dates are shown here. Any advice on what I can do? I thought about creating a calculated field to index dates but not sure how to do it. Any help is appreciated!😀


Account Service Start Stop

1 mobile jan 26 aug 14th

4 tv apr 9 sep 20th

6 radio jan 9th aug 4th

Comments

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    The STR_TO_DATE function is going to be your friend in this case. Try this for your beast modes.

    Start

    STR_TO_DATE(
      CONCAT(
        REPLACE(
          REPLACE(
            REPLACE(
              REPLACE(`Start`,'th','')
              ,'st','')
            ,'nd','')
          ,'rd','')
        ,' ',YEAR(CURRENT_DATE())
        ),'%b %d %Y')
    

    Stop

    STR_TO_DATE(
      CONCAT(
        REPLACE(
          REPLACE(
            REPLACE(
              REPLACE(`Stop`,'th','')
              ,'st','')
            ,'nd','')
          ,'rd','')
        ,' ',YEAR(CURRENT_DATE())
        ),'%b %d %Y')
    


    Here's the breakdown of what I'm doing from the inside out.

    Use the replace function multiple times to remove the th, rd, st, nd from the day of the month.

    Add the current year by using the year function and the current date function so you have a complete date.

    Use the concat function to build a string that would look something like this: Apr 4 2022

    Use the str_to_date function to convert that to a standard date. The %b %d %Y informs the function what format the string is currently in.

    Hope that makes sense.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • mroker
    mroker ⚪️
    edited October 13

    I don’t think I explained well enough. I want to show in visualization dates on an x axis and a count of active services on the y. This would be a line chart. Unfortunately now there is just a start and stop date. How can I visualize this?


    i think I need to find a way to show the months each account is active.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    Put the start date beast mode I gave you in the x-axis and put either the service field or the account field in the y-axis and then choose the aggregation type of count if it doesn't automatically do it for you. That would give you a line chart by start date.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.