1 Reply Latest reply: Apr 16, 2018 4:47 PM by David Forest

# Set equation for rolling same hour same day of week

Hi Everyone,

I am working to create a KPI that will display a metric for an hour of data, and I then need to compare it to the same hour of the same day a week ago. The equation needs to also look at the current date/time selection and update the KPI for the max hour of the current selection, and compare that to the same hour same day a week ago. I added a few entries to the autoCalendar as shown below to identify the current hour and day of week:

Hour(\$1) as [HourAsNum],

Num(WeekDay(\$1)) as [WeekDayAsNum]

I have tried the following without success:

sum({<[StartTime.autoCalendar.Date] = {"\$(=date(max(StartTime)) - 7)"}, [StartTime.autoCalendar.HourAsNum] = {"\$(=Hour(max(StartTime)) -1 )"}>}

where StartTime is the timestamp of my data set. It almost works, but does not handle just after midnight etc.. Does anyone have any ideas on how I can achieve the desired result?

thanks,

Stephen

• ###### Re: Set equation for rolling same hour same day of week

check for midnight

If(Hour(max(StartTime))=0,

sum({<[StartTime.autoCalendar.Date] = {"\$(=date(max(StartTime)) - 8)"}, [StartTime.autoCalendar.HourAsNum] = {"23"}>},

sum({<[StartTime.autoCalendar.Date] = {"\$(=date(max(StartTime)) - 7)"}, [StartTime.autoCalendar.HourAsNum] = {"\$(=Hour(max(StartTime)) -1 )"}>}

)