Activiey after a certain time in a DateTime formatted column

ShaneB
ShaneB Member
in Magic ETL

I am trying to see activity completed after 5pm, regardless of the day. I am not sure how to even begin creating this type of beastmode (if necessary). My Last_updated_date column is in a datetime format. I need to see the date but either have items color coded if after 5pm or have or have a new column added with a some sort of flag that represents any time after 5pm in the datetime column. Seeing this in a table card would be ideal. Any assistance would be greatly appreciated.

Best Answer

  • dthierjung
    dthierjung Contributor
    Answer ✓

    There's a few ways you could accomplish this, and in the ways you mentioned. Be cognizant of time zone that can effect datetimes as well as functions available in beast modes to retrieve a time. The below will work well for a card using a table visualization:

     

    Card level filter that filters on the date column, checking whether or not the time portion of the date is greater than 5pm. For instance, in a beast mode you can do this "CASE WHEN TIME(`Last_updated_date`) > TIME('17:00:00') THEN 1 ELSE 0 END". Then use this to filter for the "1" values and your card should be updated to only show those rows where last_updated_date is greater than 5pm.

     

    You can go a step further and apply custom coloring to your tables, if you wanted, which Domo provides great samples on here: https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Transforming_Data_Using_Beast_Mode/04Sample_Beast_Mode_Calculations

     

    Just scroll down to the "Conditional Formatting for a Standard Table" section. This uses some HTML though if you're not familiar with that.

Answers

  • dthierjung
    dthierjung Contributor
    Answer ✓

    There's a few ways you could accomplish this, and in the ways you mentioned. Be cognizant of time zone that can effect datetimes as well as functions available in beast modes to retrieve a time. The below will work well for a card using a table visualization:

     

    Card level filter that filters on the date column, checking whether or not the time portion of the date is greater than 5pm. For instance, in a beast mode you can do this "CASE WHEN TIME(`Last_updated_date`) > TIME('17:00:00') THEN 1 ELSE 0 END". Then use this to filter for the "1" values and your card should be updated to only show those rows where last_updated_date is greater than 5pm.

     

    You can go a step further and apply custom coloring to your tables, if you wanted, which Domo provides great samples on here: https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Transforming_Data_Using_Beast_Mode/04Sample_Beast_Mode_Calculations

     

    Just scroll down to the "Conditional Formatting for a Standard Table" section. This uses some HTML though if you're not familiar with that.

  • ShaneB
    ShaneB Member

    Perfect. Thank you so much!

Categories