Multi Line Graph with Different Time Zones

agolla2 · August 7

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 · August 7

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 · August 7

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

ArborRose · August 7

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 · August 7

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

ArborRose · August 7

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 · August 7

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 · August 7

Can anyone help me with this please?

Multi Line Graph with Different Time Zones

Answers

Categories

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
...	...	...

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
...	...	...

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
...	...	...