Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey Qlik-Gurus,
I'm in desparate need of some Set Analysis help.
I’m trying to calculate our Closed Rate % of our opportunities each month and the definition is as follows:
Numerator – Count of Opportunities Closed Won this month with any created date (the easy part)
Denominator – Count of Opps Created this month with any closed date (what I need help with)
The tricky part is there being two time dimensions (Opp Closed Month and Opp Created Month).
Here’s the current expression calculation:
=COUNT(DISTINCT if(Opp_Stage = 'Closed Won' ,OppNumber))
/COUNT(DISTINCT if(Opp_IsClosed_Flag = 'true' ,OppNumber))
And my dimension is the Opportunity Closed Year-Month – where I need to figure out how to incorporate the Opp Created Year Month dimension in the denominator. Putting a different a different set within an if statement adds another layer of complexity.
Any help or advice here would be super appreciated!
Thanks,
Andy
Andy,
I assume one issue is that your records are grouped by your Opp Closed Month, and you then miss records for current dimension Month = Opp Created Month, where Created Month <> Closed Month (assuming that closed month and created month are two fields in the same table).
You can work around this by using a common Date / Month field and distinguis between Closed and Created by a type field, transforming your source table from a cross table to a straight table, maybe like:
INPUT:
CROSSTABLE (Type, Date) LOAD * INLINE [
Opp, OppCreated, OppClosed
A, 01.09.2012, 01.09.2012
B, 01.09.2012, 01.10.2012
C, 01.08.2012, 01.10.2012
];
Then you can use your Date field as dimension and change your expression to
=count({<Type = {'OppClosed'}>} Opp) / count({<Type = {'OppCreated'}>} Opp)
{you can also include your other fields like Stage and IsClosed into these set expressions}
Hope this helps,
Stefan
Stefan,
Thanks for the help - I think we're in the right direction. We've created a date island as the new common date based on your advice. I tried an iteration of the cross table but couldn't get it to work.
Here's the updated code, but still not working:
Numerator - Count(Distinct{$<YearMonth={"Opportunity_Closed_Year_Month"},Opportunity_ClosedWone = {"true"}>} Opportunity_Number)
Denominator - COUNT(DISTINCT {$< YearMonth={"Opportunity_Created_Year_Month"},Opportunity_Closed = {"true"}>} Opportunity_Number)
Andy
Andy,
could you post the relevant script code, too? Or maybe a small sample app?
Sure thing - thanks for your help!
Opp Table:
SELECT
YEAR(a.[CREATEDDATE]) as [Opportunity_Created_Year]
,MONTH(a.[CREATEDDATE]) as [Opportunity_Created_Month]
,YEAR(a.[CLOSEDATE]) as [Opportunity_Closed_Year]
,MONTH(a.[CLOSEDATE]) as [Opportunity_Closed_Month]
,CAST(YEAR(a.[CREATEDDATE]) as varchar) + '-' + CAST(MONTH(a.[CREATEDDATE]) as varchar) as [Opportunity_Created_Year_Month]
,CAST(YEAR(a.[CLOSEDATE]) as varchar) + '-' + CAST(MONTH(a.[CLOSEDATE]) as varchar) as [Opportunity_Closed_Year_Month]
,CAST(YEAR(a.[SYSTEMMODSTAMP]) as varchar) + '-' + CAST(MONTH(a.[SYSTEMMODSTAMP]) as varchar) as [Opportunity_Mod_Year_Month]
,a.[SYSTEMMODSTAMP] as [Opportunity_Mod_Date]
,a.[CREATEDDATE] as [Opportunity_Create_Date]
,a.[CLOSEDATE] as [Opportunity_Closed_Date]
,a.[NAMEX] as [Opportunity_Name]
,a.[DESCRIPTION] as [Opportunity_Description]
,a.[STAGENAME] as [Opportunity_Stage]
,a.[ISCLOSED] as [Opportunity_IsClosed_Flag]
,a.[ISWON] as [Opportunity_IsWon_Flag]
,a.[OPPORTUNITY_ID] as [Opportunity_Number]
FROM [OPPORTUNITY] a
WHERE
a.[DELETE_FLAG] = 'N'
and a.[CREATEDDATE] > '2012-01-01'
------------------------------
Date Island:
LET varMinDate = NUM('1/1/2012');
LET varMaxDate = NUM(today(0));
TempCalendar:
LOAD
$(varMinDate)+Iterno()-1 AS Num,
Date($(varMinDate)+Iterno()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1 <= $(varMaxDate);
Calendar:
load
TempDate AS RecordDate,
date(monthstart(TempDate), 'YYYY-M') AS YearMonth
MonthEnd(TempDate)))) as LastDayOfMonth
resident TempCalendar order by TempDate Asc;
Drop table TempCalendar;
--------------------------------
Dimension:
=If(Opportunity_Closed_Year_Month = YearMonth
or Opportunity_Created_Year_Month = YearMonth
,YearMonth)
Expression:
=Count(Distinct{$<YearMonth={"Opportunity_Closed_Year_Month"},Opportunity_IsWon_Flag = {"true"}>} Opportunity_Number)
/ COUNT(DISTINCT {$< YearMonth={"Opportunity_Created_Year_Month"},Opportunity_IsClosed_Flag = {"true"}>} Opportunity_Number)
------------------------------
Goal:
calculate our Closed Rate % of our opportunities each month and the definition is as follows:
Numerator – Count of Opportunities Closed Won this month with any created date
Denominator – Count of Opps Created this month with any closed date
HI
Try like this
Count(Distinct{$<YearMonth={"$(=Max(Opportunity_Closed_Year_Month))"},Opportunity_ClosedWone = {"true"}>} Opportunity_Number)
Andy,
that's not the same approach I suggested, my approach does not create a data island. One can probably work out a solution using a data island, but I think this is not needed here (and should be avoided, since it adds complexity and is not as performant, IMHO).
Please check attached (mostly the same as posted above, tried to adapt a little closer to your table layout).
Regards,
Stefan
Hey Stefan,
Thanks for sending over the example.
On your Input table, how would I populate this portion without typing it in manually?
01.09.2012, 01.09.2012, 10.10.2012, A, Aa, ??, true, true, 1
01.09.2012, 01.10.2012, 10.10.2012, B, Bb, ??, false, false, 2
01.08.2012, 01.10.2012, 10.10.2012, C, Cc, ??, true, false, 3
01.09.2012, 01.09.2012, 10.10.2012, D, Dd, ??, false, false, 4
01.10.2012, 01.10.2012, 10.10.2012, E, Ee, ??, true, true, 5
01.08.2012, 01.10.2012, 10.10.2012, F, Ff, ??, true, false, 6
I've got around a million opportunities I need automatically inlcuded.
Thanks for your help - I really appreciate it.
Andy
That's an INLINE LOAD:
LOAD *
INLINE [
CREATEDDATE,CLOSEDATE,SYSTEMMODSTAMP,NAMEX,DESCRIPTION,STAGENAME,ISCLOSED,ISWON,OPPORTUNITY_ID
01.09.2012, 01.09.2012, 10.10.2012, A, Aa, ??, true, true, 1
01.09.2012, 01.10.2012, 10.10.2012, B, Bb, ??, false, false, 2
01.08.2012, 01.10.2012, 10.10.2012, C, Cc, ??, true, false, 3
01.09.2012, 01.09.2012, 10.10.2012, D, Dd, ??, false, false, 4
01.10.2012, 01.10.2012, 10.10.2012, E, Ee, ??, true, true, 5
01.08.2012, 01.10.2012, 10.10.2012, F, Ff, ??, true, false, 6
];
I tried to use your source table field names, so to replace the INLINE Load with your data source, just use
SELECT *
FROM [OPPORTUNITY];
If needed, adapt the field names in my sample code.
Hey Stefan,
I loaded my opportunities table and the Year_Month field are all populating null.
Any ideas?
Thanks,
Andy