Summary Number based on subtotal value

cwalliser
cwalliser Member
in Archive

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?

 

 

Comments

  • Godzilla
    Godzilla Contributor

     

    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. 

    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • kshah008
    kshah008 Contributor

    @cwalliser, did Godzilla's reply help you out?

  • cwalliser
    cwalliser Member
    I think I forgot to state in my original post that we use are doing a distinct count of records per order id. Is there a way too use this logic for distinct count?
  • kshah008
    kshah008 Contributor

    @Godzilla, any ideas on the follow up question?

    Thanks for all your help!

This discussion has been closed.

Categories