Create beast mode dates

mroker · 2022-10-13T20:20:08+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

Analyzer

Magic ETL

Comments

MarkSnodgrass

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.

mroker

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

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.