Calculate Difference between Dates for KPI Count

[anelmorales]

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.

[Kushal_Chawda · Accepted solution]

try  below

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

or

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

[anelmorales]

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!

[Kushal_Chawda]

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

[anelmorales]

Makes a lot of sense, thanks a lot! 😁