topic Re: Calculate Difference between Dates for KPI Count in QlikView

Calculate Difference between Dates for KPI Count

anelmorales — Wed, 25 Nov 2020 16:16:04 GMT

Hello all, currently I'm trying to do a Count of the number of Events which Start Date has been delayed.

I have two fields: Start Date and Actual Start Date

I want to built a KPI like this : Count(If Start Date - Actual Start Date > 30, Event_ID)

But it is not working, I need to count events that are 30 days later than the Start Date.

Re: Calculate Difference between Dates for KPI Count

Kushal_Chawda — Tue, 14 Jan 2020 21:17:13 GMT

try below

Count( aggr(If (Start Date - Actual Start Date > 30, Event_ID),Event_ID))

count({<Event_ID={"=Start Date - Actual Start Date > 30"}>}Event_ID)

I would suggest to use set analysis one if it's working as expected

Re: Calculate Difference between Dates for KPI Count

anelmorales — Tue, 14 Jan 2020 22:00:37 GMT

Hello Kush, thank you, this worked for me as I expected 🙂

I used both and they worked, I will use the one with the Set Analysis.

count({<event_id={"=Actual_Start_Date - Start_Date > 30"}>}event_id)

I have one doubt, in this Set Analysis when we have event_id = Actual_Start_Date - Start_Date > 30

To my understanding in this part of a set analysis we must input a value that should be true or false in order to return the Count(event_id). How does Qlik handles the result of the two dates difference? I hope this question makes sense.

Thanks again!

Re: Calculate Difference between Dates for KPI Count

Kushal_Chawda — Wed, 15 Jan 2020 09:30:47 GMT

Actual_Start_Date - Start_Date > 30 condition in set analysis works like a true or false only. For each eventid, it performs checks that where date difference is >30 and wherever the condition returns true aggregation functions counts the eventid. It is faster because instead of checking row by row condition, it applies the indexes wherever condition match and use that indexes to perform count

Re: Calculate Difference between Dates for KPI Count

anelmorales — Wed, 15 Jan 2020 20:46:46 GMT

Makes a lot of sense, thanks a lot! 😁