Set equation for rolling same hour same day of week

[sparkerb]

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

[dwforest]

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 )"}>}

)