Calculate KPI for 7AM and 7PM

[jorditorras]

Dear Gurus,

I have a table in QlikSense with one record per minute each day and some KPI's. Something like:

DateAndTime	Temperature
04/08/2017  8:42:00	235
04/08/2017  8:43:00	435
04/08/2017  8:44:00	254
04/08/2017  8:45:00	534
04/08/2017  8:46:00	353
04/08/2017  8:47:00	252
...	...

In the application it will be possible to select one single day. I would need to have 3 different KPI's:

Temperature of selected day at 7:00:00 AM

Temperature of selected day at 7:00:00 PM

Temperature of the next day at 7:00:00 AM.

Can anybody help me with these calculations?

Many thanks in advanced!

Regards,

Jordi

[Anil_Babu_Samineni]

But, in your sample data there is not time like 7:00 AM/PM ...??

[sunny_talwar · Accepted solution]

May be this

May be like this

Dimension

DateandTime

Expression

Only({<DateandTime = {"=Match(Hour(DateandTime), 7, 19) and Minute(DateandTime) = 0 and Second(DateandTime) = 0"}>}Temp)

[jorditorras]

Well, what I meant is:

If the user selects Date=04/08/2017,

then I'd have to show the temperature of:

04/08/2017 7:00:00,

04/08/2017 19:00:00

05/08/2018 7:00:00

Thanks,

Regards,

Jordi

[jorditorras]

Hi Sunny,

Thanks for your colaboration. It was very useful to focus the way of calculting my KPI. This wasn't exactly what I was looking for, as in this case it only shows this result when selecting specifically these Times. But your answer it correct as it fits with the way I asked. I finally created a set analysis as:

Avg({$<DateAndTime={'07:00:00'}>}[Temperature])

Thank your very much for your help!!

Regards,

Jordi