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

    Set equation for rolling same hour same day of week

    Stephen Parker

      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