3 Replies Latest reply: Oct 16, 2015 2:16 AM by neetha P RSS

    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)

       

      Any help is appreciated

        • Re: Convert SQL to Set Analysis
          Sunny Talwar

          What is the format for StageStartDate? May be its different from what date() function outputs by default. Try this maybe:

           

          =Count(Distinct {<StageStartDate={">=$(=Date(Today()-7, 'YourStageStartDateFormat'))<=$(=Date(Today(), 'YourStageStartDateFormat'))"}>} StageID)

          • Re: Convert SQL to Set Analysis
            Krishna Chaitanya

            Subtract 7  from number of date not directly from date, else convert date number obtained after subtracting 7 from date.


            edit:


            Capture.PNG


            try/see below.


            count(distinct {$<stageName ={ '4-On Bid List'},

            StageStartDate= {'>=$(=vCustomStartDate) <=$(=vCustomEndDate)'}>}StageID)

             

             

            set variables  as:

            vCustomStartDate = date(num(Date(Today()))-7,'MM/DD/YYYY')

            vCustomEndDate =  Date(Today(),'MM/DD/YYYY')


            make sure all the date are in same format.



            IF no Variables are used, then


            count(distinct {$<stageName ={ '4-On Bid List'},

            StageStartDate= {'>=$(=date(num(Date(Today()))-7,'MM/DD/YYYY')) <=$(=Date(Today(),'MM/DD/YYYY'))'}>}StageID)




            • Re: Convert SQL to Set Analysis
              neetha P

              Yes follow sunny's solution,Check if Qlikview Date format and Date Field  format are same.