topic Re: Convert SQL to set Analysis in QlikView

Convert SQL to set Analysis

Thu, 15 Oct 2015 16:01:14 GMT

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

Re: Convert SQL to set Analysis

sunny_talwar — Thu, 15 Oct 2015 16:05:47 GMT

May be this in your set analysis:

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

Re: Convert SQL to set Analysis

maxgro — Thu, 15 Oct 2015 17:57:22 GMT

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

Re: Convert SQL to set Analysis

Thu, 15 Oct 2015 20:06:33 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)