I have the need to translate timestamps to human readable strings that indicate shift names. I have a really complicated calculation that involves a bunch of nested ifelse’s. The make matters worse I have to translate the time from GMT to my timezone. Lastly I am extracting hour, minute, ect to determine if the time is between HH:MM and HH:MM. It’s nasty.
Any advice on a better way to get human readable, time zone corrected shifts out of timestamps?
There is no magic solution at the moment. (QuickSight does plan to provide timezones support but we don’t have details and timeline that we can share yet)
When I have to write complex expressions, I try to write them in small chunks and validate results along the way by showing value of the newly created fields in a table visual.
Some complex expressions can be simplified by breaking them down into a series of calculated fields that depend on each other.
create a new calculation field based on your date column and use the DATE function addDateTime(amount, period, datetime). You can add time as per your time different zone. Used the new date column on filters.
This will solve your problem till QS team share any timezone functionality in future.
You could try something like this. It’s still quite cluttered but there is only one ifelse statement. Likely pretty similar to what you’re doing now.
Very interesting. I kind of like translating these to text time (7AM - 3:30PM) instead of 1st, 2nd and 3rd.
I could definitely see that being useful too