Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculate Ratio on time difference

Hello All

I am trying to calculate a ratio on a time difference. I would like to have the ratio of all radiology reports that are finalize within a predefined goal of 24 hours compared to the total of all reports. I have attached a simple dataset that shows the topic. I have two data fields that I like to subtract from each other ("End" minus "Begin"). In the next step I would like to build the ratio of the records that are below 24 hours time limit compared to all records. In the sample file the result should be 50% because I have 5 out of 10 records with a time difference below 24 hours.

I tried this script:

count({<[(final_report) - (exam_end)]<<{1}>} [ID]) / Count([ID])

But this gives back an error: "Error in set modifier expression"

Accidentally I tried this:

count({<[(final_report) - (exam_end)]={1}>} [ID]) / Count([ID])    -> equal instead of smaller than

Then QV says "Expression OK", but this will not help me out.

I am not very skilled in the QV syntax. So may be somebody can help me out.

Thank you

Achim

1 Solution

Accepted Solutions
sunny_talwar

Try this script:

Table:

LOAD *,

  Interval(End - Begin, 'hh:mm') as Difference;

LOAD ID,

  Date#(Begin, 'DD.MM.YYYY hh:mm') as Begin,

  Date#(End, 'DD.MM.YYYY hh:mm') as End; 

LOAD * INLINE [

    ID, Begin, End

    1, 15.05.2015 10:00, 16.05.2015 09:00

    2, 16.05.2015 11:00, 17.05.2015 10:00

    3, 17.05.2015 12:00, 18.05.2015 11:00

    4, 18.05.2015 13:00, 19.05.2015 12:00

    5, 19.05.2015 14:00, 20.05.2015 13:00

    6, 15.05.2015 10:00, 16.05.2015 11:00

    7, 16.05.2015 11:00, 17.05.2015 12:00

    8, 17.05.2015 12:00, 18.05.2015 13:00

    9, 18.05.2015 13:00, 19.05.2015 14:00

    10, 19.05.2015 14:00, 20.05.2015 15:00

];

and the following expression in a text box object:

=Num(Count({<Difference = {"$(= '<=' & Interval(Interval#('24', 'hh'), 'hh:mm'))"}>}ID)/Count(ID), '#,##0.0%')

Also find attached a sample (PFA)

Best,

Sunny

View solution in original post

5 Replies
sunny_talwar

Try this script:

Table:

LOAD *,

  Interval(End - Begin, 'hh:mm') as Difference;

LOAD ID,

  Date#(Begin, 'DD.MM.YYYY hh:mm') as Begin,

  Date#(End, 'DD.MM.YYYY hh:mm') as End; 

LOAD * INLINE [

    ID, Begin, End

    1, 15.05.2015 10:00, 16.05.2015 09:00

    2, 16.05.2015 11:00, 17.05.2015 10:00

    3, 17.05.2015 12:00, 18.05.2015 11:00

    4, 18.05.2015 13:00, 19.05.2015 12:00

    5, 19.05.2015 14:00, 20.05.2015 13:00

    6, 15.05.2015 10:00, 16.05.2015 11:00

    7, 16.05.2015 11:00, 17.05.2015 12:00

    8, 17.05.2015 12:00, 18.05.2015 13:00

    9, 18.05.2015 13:00, 19.05.2015 14:00

    10, 19.05.2015 14:00, 20.05.2015 15:00

];

and the following expression in a text box object:

=Num(Count({<Difference = {"$(= '<=' & Interval(Interval#('24', 'hh'), 'hh:mm'))"}>}ID)/Count(ID), '#,##0.0%')

Also find attached a sample (PFA)

Best,

Sunny

Not applicable
Author

Hi Sunny

Thanks a lot for your help. And your script delivers exactly the result that I am looking for. I just have some problems to transfer this into my "real world". I get my productive data from an SQL-Statement via ODBC-Interface. So I cannot use the

LOAD *,

  Interval(End - Begin, 'hh:mm') as Difference

Statement as shown in your script.

So my SQL-statement looks like this:

SELECT A_UNTBEH_UEB."AUFNAHMEART" As "out/in",
    A_UNTBEH_UEB."UNTERS_SCHLUESSEL" As "ID",
    A_UNTBEH_UEB."UNTERS_BEGINN" As "date/time",
    to_char(A_UNTBEH_UEB."UNTERS_BEGINN",'YYYY') As "year",
    to_char(A_UNTBEH_UEB."UNTERS_BEGINN",'MM') As "month",
    to_char(A_UNTBEH_UEB."UNTERS_BEGINN",'IW') As "week",
    to_char(A_UNTBEH_UEB."UNTERS_BEGINN",'D') As "day",
    to_char(A_UNTBEH_UEB."UNTERS_BEGINN",'HH24') As "hour",
    to_char(A_UNTBEH_UEB."UNTERS_BEGINN",'YYYY-MM-DD') As "date",
    A_UNTBEH_UEB."UNTERS_STATUS" As "status",
    A_UNTBEH_UEB."UNTERS_ART" As "exam_shortform",
    substr(A_UNTBEH_UEB."UNTERS_ART",1,1) As "r/n",
    substr(A_UNTBEH_UEB."UNTERS_ART",2,1) As "modality",
    substr(A_UNTBEH_UEB."UNTERS_ART",4,1) As "bodyregion",
    A_UNTBEH_UEB."UNTERS_NAME" As "exam_name",
    A_UNTBEH_UEB."ABRSTELLE_KUERZEL" As "institute",
    A_UNTBEH_UEB."BEREICH_SCHLUESSEL" As "unit",
    A_UNTBEH_UEB."ARBEITSPLATZ" As "workplace",
    A_UNTBEH_SONSTIGE_FELDER."PP_MISC_MFD_1_KUERZEL" As "subspecialty",
    A_UNTBEH_UEB."UEBERWEISER_NAME" As "referrer",
    A_BEFUND."BEFUND_FREIGABE" As "final_report",
   A_UNTBEH_UEB."UNTERS_ENDE" As "exam_end"
FROM MEDORA."A_UNTBEH_UEB",
MEDORA."A_UNTBEH_SONSTIGE_FELDER",
MEDORA."A_BEFUND"

The two relevant fields are indicated in bold letters. "final_report" stands for the END-timestamp and "exam_end" stands for the BEGIN-timestamp.

But I thought I can fix that by replacing the variable "difference" in your expression like this:

=Num(Count({<Difference = {"$(= '<=' & Interval(Interval#('24', 'hh'), 'hh:mm'))"}>}ID)/Count(ID), '#,##0.0%')

replaced by

=Num(Count({<interval((final_report) - (exam_end), 'hh:mm') = {"$(= '<=' & Interval(Interval#('24', 'hh'), 'hh:mm'))"}>}ID)/Count(ID), '#,##0.0%')

But unfortunately this is not working like I expected. The graph gives me the message "No data available".

Do you have any idea, what could be the reason for that.

Thank you

Achim

sunny_talwar

Hey Achim,

I think you should be able to use a preceding load with SQL load.

Table:

LOAD *,

          Interval("final_report" - "exam_end", 'hh:mm') as Difference;

SELECT A_UNTBEH_UEB."AUFNAHMEART" As "out/in",

    A_UNTBEH_UEB."UNTERS_SCHLUESSEL" As "ID",

    A_UNTBEH_UEB."UNTERS_BEGINN" As "date/time",

    to_char(A_UNTBEH_UEB."UNTERS_BEGINN",'YYYY') As "year",

    to_char(A_UNTBEH_UEB."UNTERS_BEGINN",'MM') As "month",

    to_char(A_UNTBEH_UEB."UNTERS_BEGINN",'IW') As "week",

    to_char(A_UNTBEH_UEB."UNTERS_BEGINN",'D') As "day",

    to_char(A_UNTBEH_UEB."UNTERS_BEGINN",'HH24') As "hour",

    to_char(A_UNTBEH_UEB."UNTERS_BEGINN",'YYYY-MM-DD') As "date",

    A_UNTBEH_UEB."UNTERS_STATUS" As "status",

    A_UNTBEH_UEB."UNTERS_ART" As "exam_shortform",

    substr(A_UNTBEH_UEB."UNTERS_ART",1,1) As "r/n",

    substr(A_UNTBEH_UEB."UNTERS_ART",2,1) As "modality",

    substr(A_UNTBEH_UEB."UNTERS_ART",4,1) As "bodyregion",

    A_UNTBEH_UEB."UNTERS_NAME" As "exam_name",

    A_UNTBEH_UEB."ABRSTELLE_KUERZEL" As "institute",

    A_UNTBEH_UEB."BEREICH_SCHLUESSEL" As "unit",

    A_UNTBEH_UEB."ARBEITSPLATZ" As "workplace",

    A_UNTBEH_SONSTIGE_FELDER."PP_MISC_MFD_1_KUERZEL" As "subspecialty",

    A_UNTBEH_UEB."UEBERWEISER_NAME" As "referrer",

    A_BEFUND."BEFUND_FREIGABE" As "final_report",

  A_UNTBEH_UEB."UNTERS_ENDE" As "exam_end"

FROM MEDORA."A_UNTBEH_UEB",

MEDORA."A_UNTBEH_SONSTIGE_FELDER",

MEDORA."A_BEFUND"


Have you tried doing this???


The expression below doesn't work because you cannot yes functions on the left side of the equal sign in set analysis.

Not applicable
Author

Hi Sunny

That's great! Works perfectly.

Thank you so much.

Achim

sunny_talwar

Awesome

I am glad that it worked.

Best,

Sunny