Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
AMonot
Contributor II
Contributor II

Average between two colums containing dates

Ahoy ! 

I am trying to create a KPI which would display a single number. 

This number should be the average time that passes between two main steps, from the "Booked Date" to "Initialization Date" (in days).

Basically I have two colums, each containing a date for each row. What I need is: 

  • Calculate the difference between the "Initialisation Date" and the "Booked Date" for each row (in days)
  • Calculate the average of all the results

The tricky part is that sometimes, I do not have an "Initialisation Date", and in this case, I want my formula to disregard this row. 

I would like to present it in a KPI indicator, but I cannot seem to find the correct formula to put in.

I am so sorry for my medium English level, hope I am understandable though... 

Thanks in advance ! 

A.M.

2 Replies
Taoufiq_Zarra

@AMonot  can you share a sample and the expected output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
AMonot
Contributor II
Contributor II
Author

Hey Taoufiq, 

Thank you for your answer. 

The output expected is a KPI, a single number displayed with a title. 

Here is an example of what I want on Excel : 

AMonot_1-1640340471897.png

 

The "AVERAGE" number in orange (D10) is what I want displayed in my KPI on QlikSense. 

As you can see, there is two dates for each customer: one "Booked Date" and one "Initialization Date". I simply want to calculate the difference between those two and have the average of all the results. But I also want the formula to disregard the customers where the Initialization date is empty (like Zineya in C5) and only calculate the average on rows where there are all the data.