date difference comparison and percentage calculation - expression

[Beez]

Dear all,

Here are my sample data (like this I have 1000s of row):

Reception                          Validation                          Analyse              Delay (in minutes)

2023-07-10 08:00             2023-07-12 13:30             Analyse1              120

2023-07-11 09:45             2023-07-13 14:15             Analyse2              60

2023-07-12 10:30             2023-07-14 15:00             Analyse3              90

2023-07-13 11:15             2023-07-15 15:45             Analyse1              120

2023-07-14 12:00             2023-07-16 16:30             Analyse2              60

2023-07-15 13:30             2023-07-17 17:45             Analyse3              90

2023-07-16 14:15             2023-07-18 18:15             Analyse4              60

2023-07-10 10:00             2023-07-10 12:00             Analyse1              120

I'd like to calculate the total count of different types of analyses, such as "analyse1," "analyse2," and so on. Then, I want to find the time difference between each analysis type, measured as the interval between the validation and reception times. After that, I want to compare this interval with a predefined "delay" value for that specific type of analysis. If the calculated interval is greater than the delay value, I want to determine the percentage of analyses of that specific type that exceed the delay value. 

For instance, the Analyse1, the percentage of records where the interval value (difference between Reception and Validation) is greater than the Delay value is calculated as follows:

• There are 2 records that meet the condition (out of 3 total Analyse1 records).
• So, the percentage is (2 / 3) * 100, which is approximately 66.67%.

• For Analyse2, the percentage is approximately 50.00% (1 out of 2 records meet the condition).

• For Analyse3, the percentage is approximately 50.00% (1 out of 2 records meet the condition).

• For Analyse4, the percentage is 0.00% (no records meet the condition).

I used this expression or tried different ways but not able to achieve the desired result: 

Count(
{<Analyse={"=NetWorkDays([Reception], [Validation]) - 1 + (frac([Validation]) - frac([Reception])) > [Delay]"}>}
[Reception]
) / Count({<Analyse={"=NetWorkDays([Reception], [Validation]) - 1 + (frac([Validation]) - frac([Reception])) > [Delay]"}>} TOTAL [Reception]) * 100

Would you please help me with the qlik sense expression, how I could achieve my desired result using expression.

Thanks in advance!

[Javizh · Accepted solution]

Hello @Beez 

In your numerator, you want to count the amount of Records that fulfill a certain condition, so you need a field indicating the number of the record to be able to decide which to count

I have created an "ID" field with the RecNo() function in the data model, and the you can achive what you want.

You also dont need the total funtion in the denominator, as you want a %, you can divided by the default count.

It would be something like this:

• Count(
  {<ID={"= NetWorkDays([Reception], [Validation]) - 1 + (frac([Validation]) - frac([Reception])) > Delay/60"}>}
  [ID]
  ) / Count( [ID])

However, I get the opposite of your results with that formula.

Maybe the "<" should be a ">"?

Attached is the QVF.

Best regards.

[Beez]

Hello @Javizh 

RecNo() makes more sense. Thanks for your support and helping me to understand the logic!

Best regards