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 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())

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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

View solution in original post

3 Replies
sunny_talwar

May be this in your set analysis:

{<CreatedDate = {"$(='>' & Date((Max(CreatedDate) - 7), 'YourCreatedDateFormat') & '<=' & Date(Max(CreatedDate), 'YourCreatedDateFormat'))"}>}

maxgro
MVP
MVP

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

Not applicable
Author

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)