Caclulating Sales Close Rate with Set Analysis

[andyquirin]

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

[swuehl]

Please compare your script to the one I've attached above.

I don't create Opportunity_Closed_Year_Month / Opportunity_Created_Year_Month at all. These fields are replaced by Opp_Type and Year_Month in the CROSSTABLE Load.