Summary Number based on subtotal value

cwalliser

I have a card where I show the ratio of opportunities closed to opportunities available in each quarter.

That part is easy.

 

The card requestor would also like to see the average ratio closed/available for the last 4 quarters combined, on the same card,

 

My first idea was to use the summary feature for this. But that appears to include either the data for the entire data set, or only the data for the current time period.

 

Is there a way to show a summary value for only and specified subset of the data?

1. The entire data set covers from 2011 to current.

 

2. When reporting for today, selection criteria is set to the dates for 2015-Q1 to 2016-Q2. With ratio per Qtr.

 

3. The 4 Qtr ratio would be the opportunities closed / available for the total of current qtr (2016-Q2) back to 2015-Q3

 

Any ideas?

 

 

Godzilla

 

you can use a beast mode field to calculate the ratio where you would only include records where the Opportunity date is between certain dates. in your calculation you would have a case statement to check for the dates. 

 

assuming you're loading the opportunity records with a Date and OppertunityStatus fields, you can do something like this:

 

Sum(case when `Date` >= '2015-01-01' and `OpportunityStatus` = 'Closed' then 1 else 0 end) / Sum(case when `Date` >= '2015-01-01'  then 1 else 0 end)

 

You probably want to figure out how to calculate the start date using a beast mode formula. If not, you can hard code the date since you only have to change this once a quarter. 

 

In the beast mode window, make sure to check the "Apply to Summary Number" checkbox. Then use this field as your summary number field and set the summary number to use all values.