KPI Average days in a specified status

[CA2]

Hi, I new to Qliksense desktop and I'd like some help please. I'd like to know how to create a formula to determine the average number of working days for items in a status of waiting user . How do I do that?

This average will be the KPI to assess against the standard of 10 working days to get a reply from the user, which if it is acceptable will move the item to the assessment stage.

Here's the sample data:

[lorenzoconforti]

Are you looking for the average number of days from created to today? See attached example

=avg({<Status = {"Waiting user"}>} Today()-Created)

[Taoufiq_Zarra]

can you give an example for this sample data ?

[CA2]

Hi, thanks for responding. Your suggested formula only returns just the difference between today's date and one of the oldest created date. Whereas, the formula should calculate the networks days for each item with  the status of "Waiting User" and then find the average of all. Any ideas?

[lorenzoconforti · Accepted solution]

See attached

[CA2]

Hi, 

It took me a while but now I know that dragging & dropping a qvf file works to see the formula you provided. Thank you!

I used your formula, which shows as OK but after I apply it no results are returned. I double-checked and changed the Created field to be date stamp only but still no results for the Average KPI.  Hmmm.....

[lorenzoconforti]

Can you post your qvf file?

[CA2]

Hi, I can't as my file contains some sensitive data. By chance would the position of the ID column make any difference?
That's the only thing which is different in your sample file & mine...

[Kushal_Chawda]

Can you try this?

set the Date format as per the create date format in your data

SET TimestampFormat='DD/MM/YYYY h:mm';

then use below expression

=avg(aggr(networkdays(CreateDate,today()),ID))

[CA2]

Hi Kush,

I tried your suggestion- still no results displayed...