topic Re: Calculate KPI for 7AM and 7PM in QlikView

Calculate KPI for 7AM and 7PM

jorditorras — Tue, 31 Oct 2017 15:50:04 GMT

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

Re: Calculate KPI for 7AM and 7PM

Anil_Babu_Samineni — Tue, 31 Oct 2017 16:21:00 GMT

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

Re: Calculate KPI for 7AM and 7PM

sunny_talwar — Tue, 31 Oct 2017 16:24:15 GMT

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)

Re: Calculate KPI for 7AM and 7PM

jorditorras — Thu, 02 Nov 2017 11:37:41 GMT

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

Re: Calculate KPI for 7AM and 7PM

jorditorras — Fri, 03 Nov 2017 13:58:27 GMT

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