topic Re: Calculate Ratio on time difference in QlikView

Calculate Ratio on time difference

Fri, 15 May 2015 15:05:54 GMT

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

Re: Calculate Ratio on time difference

sunny_talwar — Fri, 15 May 2015 15:37:34 GMT

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

Re: Calculate Ratio on time difference

Wed, 20 May 2015 12:53:54 GMT

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

Re: Calculate Ratio on time difference

sunny_talwar — Wed, 20 May 2015 13:00:31 GMT

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.

Re: Calculate Ratio on time difference

Wed, 20 May 2015 13:40:46 GMT

Hi Sunny

That's great! Works perfectly.

Thank you so much.

Achim

Re: Calculate Ratio on time difference

sunny_talwar — Wed, 20 May 2015 13:41:58 GMT

Awesome

I am glad that it worked.

Best,

Sunny