Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
Yes follow sunny's solution,Check if Qlikview Date format and Date Field format are same.