6 Replies Latest reply: Jun 26, 2015 6:50 PM by Sinan Ozdemir RSS

    Problem with Averages

    Chris Benham

      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
          Jonathan Dienst

          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
              Jonathan Dienst

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

              • Re: Problem with Averages
                Chris Benham

                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);