I need to create a custom field that passes the value “Operational” if the last known time stamp occurred within the past 24 hours and the value “Unreachable” if the last known time stamp is longer than 24 hours.
With max(‘your date column’) and now() in a calculated field you should be able to display what you want. Something like:
ifelse(
dateDiff(max(datetime), now(), ‘HH’) <= 24,
‘Operational’,
‘Unreachable’
)
1 Like
Thanks @Jeroen. I’m able to save the custom field but get the attached error when attempting to use the fields in a visualization.
Hi @todd.hoffman . Not 100% sure, but I guess you created the calculated field as part of your dataset. Try to create it as part of your analysis. If that’s not possible I’m sorry but I cannot resolve your problem.
To me it’s also unclear why you can do some calculated queries as part of your analysis but not in your dataset (or the other way around)
Hi, @todd.hoffman. Did @Jeroen’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!
Here’s a slight modification, try:
ifelse(
dateDiff(maxOver(datetime, [any partition or group by fields you want or can leave empty square brackets if you want the max date in the entire data set], PRE_AGG), now(), ‘HH’) <= 24,
‘Operational’,
‘Unreachable’
)
(ignore the bold in the formatting above - not intentional)
1 Like