Converting UTC to event time zone and then distinguishing between 'after hours' and 'business hours'

[ammarahw]

I need to convert a timestamp from UTC to the time zone where the event occurred and then create a field that says whether that time is in Business Hours or After Hours. This is the calculation I currently have for this but it times out when I put it into a visual:

=IF((HOUR(ConvertToLocalTime(call_start_time, time_zone)) >= 8 AND HOUR(ConvertToLocalTime(call_start_time, time_zone)) <= 17), 'Business Hours', 'After Hours')

I have also tried creating a measure called Call Local Time that is:

ConvertToLocalTime(call_start_time, time_zone)

then plugging this calculation into

=IF((HOUR(Call Local Time)) >= 8 AND HOUR(Call Local Time)) <= 17), 'Business Hours', 'After Hours')

The problem is that with the measure being plugged into the dimension the result doesn't seem accurate as it starts calling everything After Hours despite there actually being more of a 50/50 split between after hours and business hours. 

I was wondering if its possible to write the calculation in the Data Load Editor and if so, how would I do it? 

[Levi_Turner · Accepted solution]

Using the same logic as the front-end, but as a preceding load (docs, examples). In your example, let's assume that you already have the timestamp converted to the timezone you are interested in:

// Variables for random timestamp generation
Let vStart = Time#('00:00','hh:mm');
Let vEnd = Time#('23:59','hh:mm');
Let vStartMinute = num('$(vStart)')*24*60;
Let vEndMinute = num('$(vEnd)')*24*60;

[timestamps]:
// Use a preceding load to apply the logic of business hours being between 08:00 and 17:59
LOAD
	[timestamp],
    IF(HOUR([timestamp]) >= 8 AND HOUR([timestamp]) <= 17, 'Business Hours','After Hours') AS [IsBusinessHours];
// Create timestamps for 00:00 --> 23:59
LOAD
	Timestamp(Temp_Time/1440) as [timestamp];
LOAD
	$(vStartMinute) -1+  IterNo() as Temp_Time
AutoGenerate 1 While $(vStartMinute) + IterNo() -1<=  $(vEndMinute);

Resulting in this:

[ammarahw]

Thank you, I was able to make my above solution work by limiting the data to the last 30 days but I am going to try this to see if it performs better