Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sparkerb
New Contributor II

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

1 Reply
dwforest
Valued Contributor

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

)

Community Browser