Case Statement (dividing by specific conditions)

John-Peddle

Good afternoon, Dojo's. We received assistance in another thread (https://goo.gl/i5cVjC - thank you @RGranada!) but needed to expand upon the original beast mode to determine a completed percentage for only certain individuals as opposed to company-wide and @Valiant suggested it's best to open a new thread for this request for visibility.

 

Here goes! The original solution worked but we didn't realize at the time of the request that it would divide the specified agents by a company-wide total, and not the total of just the offers that Jane Doe or John Doe had worked themselves (since one of the fields used was a date, we made one change - Settled from SUM to COUNT).

 

We've attempted to update the second part of the original beast mode, trying dozens of variations based on the initial solution; while most attempts validate, they always return an error message ("You have a divide by zero error in your Card’s Beast Mode") when added as a series. We have the ability to use either Settled1 (date field) or Settled2 (a 1 or 0).

 

Below is the company-wide beast mode that works for all departments, including ours (1), the original Beast Mode w/update mentioned above (2), and a couple of the attempts made to isolate just our call center group (3 & 4); hoping someone can help identify the error(s).

 

Please let us know if more infomration is required, and as always, thanks in advance for your time & assistance!  

 

1)

Company-wide Conversion% (settled and completed would be either 1's or 0's
CASE

WHEN SUM(`Settled`) = 0 THEN 0 ELSE

(SUM(`Completed`) / SUM(`Settled`))

END

 

2)

Call Center ONLY (specific agents piece works, but dividing by company-wide settled):

COUNT(CASE WHEN `Owner` = 'Jane Doe' OR `Owner` = 'John Doe' THEN `Owner` END)

/ 

NULLIF(COUNT(`Settled`),0)

 

3)

COUNT(CASE WHEN `Owner` = 'Jane Doe' OR `OfferOwner` = 'John Doe' THEN `Owner` END)

/ 

NULLIF(COUNT(`Settled` = '1') AND `Owner` = 'Jane Doe' OR `Owner` = 'John Doe'),0

 

4)

CASE WHEN COUNT(`Settled`) = 0 THEN 0  ELSE

COUNT(CASE WHEN `Owner` = 'Jane Doe' OR `Owner` = 'John Doe' THEN `Owner` END)

/

COUNT(CASE WHEN `Owner` = 'Jane Doe' OR `Owner` = 'John Doe' THEN `Settled` END) 

 

RGranada

Hi @John-Peddle,

 

I think the problem on @Valiant code is that it has an Aggregation deep in 2 nesting CASE WHEN statements, from my experience that is something DOMO just, doesn't like...

 

Now if I correctly understood your problem, this code should help you:

 

SUM(CASE WHEN `Owner` IN ('Jane Doe','John Doe') THEN `Completedoffers` END)
/
NULLIF(
SUM(CASE WHEN `Owner` IN ('Jane Doe','John Doe') THEN `SettledOffers` END) +
SUM(CASE WHEN `Owner` IN ('Jane Doe','John Doe') THEN `Completedoffers` END) ,0)

This should give you zero when owner not IN ('Jane Doe','John Doe') , but , if you don't want this to happen you can try the following :

 

NULLIF(
SUM(CASE WHEN `Owner` IN ('Jane Doe','John Doe') THEN `Completedoffers` END)
/
NULLIF(
SUM(CASE WHEN `Owner` IN ('Jane Doe','John Doe') THEN `SettledOffers` END) +
SUM(CASE WHEN `Owner` IN ('Jane Doe','John Doe') THEN `Completedoffers` END) ,0)
,0)

Hope it helps, tell me how it goes.

Valiant

I'm not 100% sure if this is what you're looking for, but here goes.

CASE WHEN `Owner` = 'Jane Doe' OR `Owner` = 'John Doe' 

THEN

     CASE

     WHEN SUM(`Settled`) = 0 THEN 0 ELSE

     (SUM(`Completed`) / SUM(`Settled`))

     END

END

 

 

This would prevent the divide by 0 error and would only run on rows that match your Owner column.

 

Is that what you were trying to accomplish?

Sincerely,

ValiantSpur

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

John-Peddle

Thanks for the quick reply, @Valiant. The beast mode validates but once added as a series it returns a different error message this time (a very vague message, so not really sure what the issue might be): "An issue has occurred during processing. We are unable to complete the request at this time."

 

Sorry for not thinking of this sooner, but wondering if viewing what the dataset itself might look like would be more helpful than that mess I originally posted!

 

In the example below, Jane, John, & Jim are in our department, Jean & Jill are in another one so we'd only want to find the completion conversion percentage for Jane, John, & Jim as opposed to the entire company & would be Completed / Settled (any offer that has either been Completed OR Canceled) - guessing we could use either SettledOffer or SettledDate, depending on the syntax of the beast mode, no?).

 

Hope this helps to clarify the ask! Thanks!

 

 

 

 

 

 

RGranada

Hi @John-Peddle,

 

I think the problem on @Valiant code is that it has an Aggregation deep in 2 nesting CASE WHEN statements, from my experience that is something DOMO just, doesn't like...

 

Now if I correctly understood your problem, this code should help you:

 

SUM(CASE WHEN `Owner` IN ('Jane Doe','John Doe') THEN `Completedoffers` END)
/
NULLIF(
SUM(CASE WHEN `Owner` IN ('Jane Doe','John Doe') THEN `SettledOffers` END) +
SUM(CASE WHEN `Owner` IN ('Jane Doe','John Doe') THEN `Completedoffers` END) ,0)

This should give you zero when owner not IN ('Jane Doe','John Doe') , but , if you don't want this to happen you can try the following :

 

NULLIF(
SUM(CASE WHEN `Owner` IN ('Jane Doe','John Doe') THEN `Completedoffers` END)
/
NULLIF(
SUM(CASE WHEN `Owner` IN ('Jane Doe','John Doe') THEN `SettledOffers` END) +
SUM(CASE WHEN `Owner` IN ('Jane Doe','John Doe') THEN `Completedoffers` END) ,0)
,0)

Hope it helps, tell me how it goes.

John-Peddle

Thanks for the response, @RGranada. The formula validates and provides results though they vary when compared to selecting just those agents from the company wide quick filter at the top level, so we'll look into that a bit more on our end unless you have any thoughts on this?

 

And no worries if not, we really appreciate the time & assistance you both provided with this request, @RGranada and @Valiant.  

RGranada

No problem @John-Peddle.

 

With the formula I provided if you filter the same agents at top level, the results do change? Or is it a diferent formula when you filter the agents at top level?

 

Regards,

John-Peddle

@RGranada - the original beast mode formula was to determine Completed Conversion % company wide, as follows:

CASE
WHEN SUM(`SettledOffers`) = 0 THEN 0
ELSE (SUM(`CompletedOffers`)/ SUM(`SettledOffers`))
END

 

It's a MEASURE being added as a SERIES (Line + Grouped Bar chart), and our Y Axis is the total # of offers company wide. We added the beast mode provided as the second series to have a company-wide completion % side-by-side with one for the selected agents in our call center. 

 

However, when unchecking Select ALL from OfferOwner, and then indivudally selecting only agents included in the new beast mode the two columns provided different completed conversion %'s (variations see ragned from were 8%-15% in any given month over the last 12 months). 

 

 

RGranada

I personally don't like that type of aggregation filters...

 

Can you try this, and check the numbers...

 

IFNULL(

SUM(`CompletedOffers`)

/

NULLIF(SUM(`SettledOffers`),0),0)

 

If you don't need the zero in case `SettledOffers` are zero and can live will null use :

 

SUM(`CompletedOffers`)

/

NULLIF(SUM(`SettledOffers`),0)

 

Any of the formulas will avoid divide by zero problems, because fortunately in most SQL flavors

X/0 = ERROR but X/NULL = NULL, I think this is a very important concept when doing divisions in Beast Mode (or SQL in general)

 

It should, in theory, get the same results of your formula but without aggregations inside conditional elements.

 

Hope this helps.Tell me how it does.

John-Peddle

Thanks @RGranada. Using the updated formula returned the same completion% as the one we're currently using, and we still see that variance when comparing the previous formula (for specific agents only) and only checking them off on the quick filter (with the company-wide beast mode). 

 

We're missing something on our end which is causing the variance between the new beast mode and the existing one when including only certain agents (i.e. Sr Agents) via Quick Filters, we'll have to dig further on that (see the screenshot below, this is what we're seeing). 

 

And I do understand your thoughts on aggregation filters and the way our beast mode is currently written; I've forwarded that concern along to leadership for review since they work more closely with our Engineering team on these issues, and I'm not authorized to make these changes.