Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

3 Replies
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)

krishna_2644
Specialist III
Specialist III

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)




Anonymous
Not applicable
Author

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