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.



      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 Nagulapally

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



            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.