topic Re: Problem with Averages in QlikView

Problem with Averages

Fri, 05 Jun 2015 10:29:51 GMT

Hi all,

I have been experiencing a problem with calculation of average turnaround times for a little while now & I must admit, im starting to run out of ideas !.

The basic problem is that I am trying to replicate results which are currently obtained via an SQL statement, which I don't seem to be able to put straight into QV. To make matters worse, we are working with a paradox database - so I cant easily see a 'top down' view of what is where, as is the case with SQL.

In any case, the following is the SQL that is being used currently:

SELECT S.Client,

R.Test,

S.Type,

MONTHNAME(recdate) AS Received_Month,

Sum (D.KPIREPEAT) As Repeats,

Sum (D.KPITEST) As Tests,

AVG(R.Approved-S.sampdate) As TotalTime,

AVG(R.Approved-S.Recdate) As TotalTime_FromReceipt,

AVG(S.logdate-S.recdate) As Total_Time_In_Prep,

AVG(R.Detdate-S.logdate) As TotalTime_InLab,

AVG(R.Approved-R.Detdate) As Time_Lab_to_Approval

FROM Samples S JOIN Results R on S.sampnum=R.sampnum Join Dets D on R.det=D.det

Where S.RECDATE>=?1 AND S.RECDATE<=?2 AND S.Sampstatus='APPROVED'

AND (D.KPITEST=1 OR D.KPIREPEAT=1)

Group By 1,2,3,4

UNION ALL

SELECT S.Client,

R.Test,

S.Type,

MONTHNAME(recdate) AS Received_Month,

Sum (D.KPIREPEAT) As Repeats,

Count (Distinct S.Sampnum) As Tests,

AVG(R.Approved-S.sampdate) As TotalTime,

AVG(R.Approved-S.Recdate) As TotalTime_FromReceipt,

AVG(S.logdate-S.recdate) As Total_Time_In_Prep,

AVG(R.Detdate-S.logdate) As TotalTime_InLab,

AVG(R.Approved-R.Detdate) As Time_Lab_to_Approval

FROM Samples S JOIN Results R on S.sampnum=R.sampnum Join Dets D on R.det=D.det

Where S.RECDATE>=?1 AND S.RECDATE<=?2 AND S.Sampstatus='APPROVED'

AND (R.Test='SIZING' OR R.Test='ICPTM')

Group By 1,2,3,4

With regards QV, I have pulled in each table separately (i.e: Tests,Results,Samples & Dets) & made sure they are linked correctly. Then, I have a straight table that im using, with received month (RECMONTH) as the dimension & AVG(approved-sampdate) as an example, for total time.

I cant see where im going wrong, but the results in QV are totally different to the current query. I think this is most likely down to my inexperience with complex sql. If anyone can help or point me in the right direction, it would be most appreciated

Re: Problem with Averages

jonathandienst — Fri, 05 Jun 2015 11:20:57 GMT

I suggest that you post the script you have already tried.

QV variable, table and field names are case sensitive, so RecDate, recdate and RECDATE are seen as separate fields. Could this be part of your problem?

Re: Problem with Averages

jonathandienst — Fri, 05 Jun 2015 11:22:24 GMT

How are you handling the parameterised part of the query (the ?1 and ?2 date values)?

Re: Problem with Averages

Fri, 05 Jun 2015 11:28:47 GMT

Hi Jonathan, here is my full load script - with the parameterised part of the query, im just selecting the required received date

Results:

LOAD
*,
APPROVED-DETDATE as TimeLab_ToApproval;

LOAD

APPROVED,
Date (APPROVED) as APPROVED_DATE,
Date(DETDATE) as DETDATE,
DET,
SAMPNUM,
FROM
[..\..\..\QVData TEST\Energy Services\Data\Results.QVD]
(qvd)

WHERE Date(DETDATE)>'14/01/2015';

Samples:

LOAD

Date(APPDATE) as APPDATE,

Lookup('APPROVED',SAMPNUM,SAMPNUM,'Results') as Approved,
Lookup('DETDATE',SAMPNUM,SAMPNUM,'Results') as DetDate,

CLIENT,
CLIENTSREF,
LAB_TO,
date(LOGDATE) as LOG_DATE,
LOGDATE as LOGDATE,
RECDATE,

date (RECDATE) as REC_DATE,
Month(RECDATE) as RECMONTH,

year(RECDATE) as RECYR ,

SAMPDATE,
date (SAMPDATE) as SAMP_DATE,
month (SAMPDATE) as SAMPMONTH,

SAMPNUM,
SAMPSTATUS,
TURNAROUND,
TURNDAYS,
TYPE as SAMPLETYPE,

WEEKNUM
FROM
[..\..\..\QVData TEST\Energy Services\Data\Samples.QVD]
(qvd)

where SAMPSTATUS='APPROVED' and Date(RECDATE)>'14/01/2015' ;

Averages:

LOAD
//*,
SAMPLETYPE,
Month(RECDATE) as Recieved_Month,
CLIENT as Client_Averages,
APPDATE-SAMPDATE As TotalTime,
Approved-RECDATE as TotalTime_FromReciept,
LOGDATE-RECDATE as TotalTime_InPrep,
DetDate-LOGDATE as TotalTime_InLab
Resident Samples;

Store Averages into \\uk01-apv016\C$\Qlikview Test\QVData TEST\Energy Services\Data\Averages.QVD(qvd);

Dets:

LOAD

CATEGORY,
DET,
DETNAME,
DETORDER,
DETSTATUS ,
EXCLUDE,
KPITEST,
KPIREPEAT,
RPTGROUP,
TESTDATE

FROM
[..\..\..\QVData TEST\Energy Services\Data\Dets.QVD]
(qvd);

Tests:
LOAD

ANALYSTS,
TEST,
TEST_NAME,
TEST_REF,
TESTSTATUS
FROM
[..\..\..\QVData TEST\Energy Services\Data\Tests.QVD]
(qvd);

Re: Problem with Averages

jonathandienst — Fri, 05 Jun 2015 11:51:11 GMT

This is more different to your original post than it is similar - different fields, another table, different conditions and QVD rather than SQL sourced. So I am not sure how to answer your question which was to replicate the SQL (union and join) in QV.

Re: Problem with Averages

Fri, 05 Jun 2015 12:08:51 GMT

Hi Jonathan,

There are more fields etc, but the same principal still applies - I just need to replicate the SQL statements in QV, because I cant simply copy & paste into the script

Re: Problem with Averages

sinanozdemir — Fri, 26 Jun 2015 22:50:19 GMT

Have you tried to use Avg with Aggr in a straight table? Because SQL computes the average based on the attributes that are pulled into the query.

So maybe try like this: Aggr(Avg(your facts), Client, Test, Type, Monthname).

Hope this helps