topic Re: Convert SQL to Set Analysis in QlikView

Convert SQL to Set Analysis

Thu, 15 Oct 2015 20:21:21 GMT

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 — Thu, 15 Oct 2015 20:23:43 GMT

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

Qrishna — Thu, 15 Oct 2015 20:35:54 GMT

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

edit:

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

Anonymous — Fri, 16 Oct 2015 06:16:50 GMT

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