Multi Line Graph with Different Time Zones

agolla2

Hello,

I have a dataset here where there are 4 time zones, EST, CST, MST, PST and the fields are this CST Created Date, MST Created Date, PST Created Date, Created Date Last one is EST by default.

Example format: 2019-03-11 10:22:17

So, I have extracted the hour using HOUR (`MST Created Date`) and for others as well. But I need a multi-line graph where x axis would be 0 to 23 and I need 4 lines for each time zone, and I want to count one particular field from my dataset.

How to achieve this in domo?

ArborRose

Make sure your dataset has columns for the hour extracted from each time zone's created date (CST Hour, MST Hour, PST Hour, and EST Hour). Create a Magic ETL to aggregate your data by hour for each time zone, Group by with the count of your fields for each time zone.

Then use a multi line graph and set the x-axis for hours from 0 to 23. On the y-axis, display the aggregate count.

agolla2

Thanks for the response, instead of creating an additional dataset can I write a series beast mode formula and achieve this?

ArborRose

Yes, I think you could use beast mode. Build some calculations for each time zone, and pivot the data to get everything in a single column. I'd have to create some sample data and try it to give a better answer.

Beast Mode for EST:
HOUR(Created Date)

Beast Mode for CST:
HOUR(CST Created Date)

Beast Mode for MST:
HOUR(MST Created Date)

Beast Mode for PST:
HOUR(PST Created Date)

CASE

    WHEN `Time_Zone` = 'EST' THEN HOUR(`Created Date`)

    WHEN `Time_Zone` = 'CST' THEN HOUR(`CST Created Date`)

    WHEN `Time_Zone` = 'MST' THEN HOUR(`MST Created Date`)

    WHEN `Time_Zone` = 'PST' THEN HOUR(`PST Created Date`)

END

agolla2

I did not understand the solution, how would my final dataset look?

ArborRose

I would need to do it see it more specifically.

Hour	Time_Zone	Count
0	EST	10
1	EST	15
2	EST	8
...	...	...
0	CST	12
1	CST	9
2	CST	11
...	...	...
0	MST	7
1	MST	14
2	MST	13
...	...	...
0	PST	5
1	PST	10
2	PST	9
...	...	...

Group by Hour and Time_Zone. Aggregate the counts of your specific field.

Create a new card and select the "Multi-Line Chart" type. Drag the Hour field to the X-axis. Drag the Time_Zone field to the Series. Drag the field you want to count to the Y-axis.

agolla2

Thanks for the response.

But I have my data like this in table format how to show this in 4-line graphs (each time zone) using a beast mode in the existing dataset rather than creating a new one.

agolla2

Can anyone help me with this please?