Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create a straight table in qlikview and one of the columns is New Opportunities created in the last 7 days. I am able to use the below SQL query to get the results from the database, but when I try to use this in the expressions tab on the Chart Properties in Qlikview it does not work. Any help is appreciated
select IncidentID, CreatedDate as NewOpportunity
from tblIncident
where CreatedDate >= DATEADD(day,-7, GETDATE())
if I understand, to replace your SQL with set analysis
- in the script don't filter the record
SQL
select IncidentID, CreatedDate
from tblIncident;
- you can create a chart with
dimension CreatedDate
expression count(distinct {<CreatedDate={">=$(=Date(Today()-7))<=$(=Date(Today()))"}>} IncidentID)
the expression is with set analysis and filter the CreatedDate between today-7 and today
You can find a small example (of set analysis) in the attachment
May be this in your set analysis:
{<CreatedDate = {"$(='>' & Date((Max(CreatedDate) - 7), 'YourCreatedDateFormat') & '<=' & Date(Max(CreatedDate), 'YourCreatedDateFormat'))"}>}
if I understand, to replace your SQL with set analysis
- in the script don't filter the record
SQL
select IncidentID, CreatedDate
from tblIncident;
- you can create a chart with
dimension CreatedDate
expression count(distinct {<CreatedDate={">=$(=Date(Today()-7))<=$(=Date(Today()))"}>} IncidentID)
the expression is with set analysis and filter the CreatedDate between today-7 and today
You can find a small example (of set analysis) in the attachment
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)