3 Replies Latest reply: Oct 15, 2015 4:06 PM by Toedtli Brian RSS

    Convert SQL to set Analysis

      I am trying to create a straight table in qlikview and one of the columns is New Opportunities created in the last 7 days.  I am able to use the below SQL query to get the results from the database, but when I try to use this in the expressions tab on the Chart Properties in Qlikview it does not work.  Any help is appreciated

       

      select IncidentID, CreatedDate as NewOpportunity

      from tblIncident

      where CreatedDate >= DATEADD(day,-7, GETDATE())

        • Re: Convert SQL to set Analysis
          Sunny Talwar

          May be this in your set analysis:

           

          {<CreatedDate = {"$(='>' & Date((Max(CreatedDate) - 7), 'YourCreatedDateFormat') & '<=' & Date(Max(CreatedDate), 'YourCreatedDateFormat'))"}>}

          • Re: Convert SQL to set Analysis
            Massimo Grossi

            if I understand, to replace your SQL with set analysis

             

            - in the script don't filter the record

            SQL

            select IncidentID, CreatedDate

            from tblIncident;

             

            - you can create a chart with

            dimension    CreatedDate

            expression    count(distinct {<CreatedDate={">=$(=Date(Today()-7))<=$(=Date(Today()))"}>} IncidentID)

             

            the expression is with set analysis and filter the CreatedDate between today-7 and today

             

            You can find a small example (of set analysis) in the attachment

            • Re: Convert SQL to set Analysis

              I am also trying to show how many opportunities where changed to a certain stage during the last 7 days.  I tried the below set analysis, but it does not work.  I have also included the SQL that works correctly.

               

              SQL:

              select stageName, stageID, startDate

              from tblIncidentStageHistoryReport

              where startDate >= DATEADD(day,-7, GETDATE())

              and stageName = '4-On Bid List';

               

              Set Analysis:

              count(distinct {<StageStartDate={">=$(=Date(Today()-7))<=$(=Date(Today()))"}>} StageID)