Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Analyse1
records).(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!
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:
However, I get the opposite of your results with that formula.
Maybe the "<" should be a ">"?
Attached is the QVF.
Best regards.
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:
However, I get the opposite of your results with that formula.
Maybe the "<" should be a ">"?
Attached is the QVF.
Best regards.
Hello @Javizh
RecNo() makes more sense. Thanks for your support and helping me to understand the logic!
Best regards