Hello,
I am working on a requirement and trying to convert a SQL query to Qliksense KPI expression. I have all the fields used in the query in the data model. SQL is:
*****************************************************
--Survey completions at start of program
--Get first month for new customer
select
a.CustomerID,
a.ReportingMonth,
a.Survey
into #NEWCUSTOMER
from ABC a
where a.ReportingMonth between @StartMonth and @EndMonth
and a.ReportingMonth = cast(year(a.StartDate) as nvarchar) + format(month(a.StartDate), '00')
and a.New_Customer = 1
--Add second month to new customer records
insert into #NEWCUSTOMER
select
a.CustomerID,
a.ReportingMonth,
a.Survey
from ABC a, #NEWCUSTOMER n
where a.ReportingMonth between @StartMonth and @EndMonth
and a.CustomerID = n.CustomerID
and a.ReportingMonth = format(dateadd(month, 1, datefromParts(left(n.ReportingMonth,4), right(n.ReportingMonth, 2), 1)), 'yyyyMM')
--Return number of new customer with Survey and proportion of new customers
select
count(distinct na.CustomerID) NewConsumers,
format(count(distinct na.CustomerID) * 1.0 /
(select count(distinct n.CustomerID) from #NEWCUSTOMER n), '0%') ProportionOfNew
from #NEWCUSTOMER na
where na.Survey = 1
*********************************************************
I am struggling to get the right aggregation to get the count of customer who completed Survey in 2nd month as well.
Appreciate any leads. Thanks!
