Show sum of two averages in summary number (already exists in nested coumn)?

Thought this would be easy but haven't been able to get it so that BOTH of the average times for a `status` ('break' and 'unscheduled break'), which are part of a nested bar chart, can be used in the summary number - currently, we've only been able to get it to show 'break, not both totaled as is visible in the chart. None of the available measures seem to provide the sum for both which is why we're assuming it would require a beast mode.

Done several key word searches but didn't see that this was asked before; any thoughts on what we're missing here? It has to be an aggregation, correct? Again, thought this would be really easy & what's needed would be clearly visible since the chart itself shows the sum of both numbers but it's proving to be a royal pain & we can't figure it out. Thanks in advance for any assistance; please let us know if you'll need any additional information.

A couple of things we've we've tried so far that validate (several others didn't validate):

This one shows ONLY 'Break', which is 14.84, in the summary# -

AVG(CASE WHEN `Status` = 'Break' THEN `TotalDurationPerdayInMinutes` END) + AVG(CASE WHEN `Status` = 'Unscheduled Break' THEN `TotalDurationPerdayInMinutes` END)

This one is showing 133.52, unclear on this except that it's incorrect - .

SUM(CASE WHEN `Status`='Break' THEN `TotalDurationPerdayInSeconds` / 60 ELSE 0 END) + SUM(CASE WHEN `Status`='Unscheduled Break' THEN `TotalDurationPerdayInSeconds` / 60 ELSE 0 END)

Screen Shot 2018-04-02 at 2.17.23 PM.png

Find more posts tagged with

Accepted answers

John-Peddle

@ST_-Superman-_, tried what you said but that returned a couple different results based on the beast mode used, neither of which was correct.

The first was using one of the beast modes you provided earlier, and just making a quick update to multiply by 2; here's what we get:

Screen Shot 2018-04-03 at 2.39.57 PM.png

We also attempted to use something more simplified, trying AVG(`TotalDurationPerdayInMinutes`) * 2 and this is what the Summary Number returned when using that:

Screen Shot 2018-04-03 at 2.43.58 PM.png

** Additionally, we heard back from Domo Support and this is what they had to say: "Unfortunately you will not be able to make this work inside of a card. You will need to perform this calculation inside of DataFlow to create a column for your Summary Number. Your case has been assigned to our DataFlow team for further review."

Unfortunately, appears this isn't something that can be done within the card using a beast mode even though that would seem entirely possible. But again, thanks to you and Aaron (@AS) for your assistance; even though it didn't work out it was a learning experience for me and another solid effort from the Dojo Community! Smiley Happy

John-Peddle

For anyone interested in this thread, or maybe looking to do something similar in a card: heard back from Domo Support (Joe E.) today, and he was able to create a beast mode that provides an average of two averages within the summary number that works perfectly!

This calculation provides us Break & Unscheduled Break, combined, as it appears in the nested bar graph for the current month. He advised the issue was whitespace, and he added the TRIM function to his beast mode.

AVG(CASE WHEN TRIM(`Status`) = 'Break' AND MONTH(`Date`) = MONTH(CURRENT_DATE()) AND YEAR(`Date`) = YEAR(CURRENT_DATE()) THEN `TotalDurationPerdayInMinutes` END)
+
AVG(CASE WHEN TRIM(`Status`) = 'Unscheduled Break' AND MONTH(`Date`) = MONTH(CURRENT_DATE()) AND YEAR(`Date`) = YEAR(CURRENT_DATE()) THEN `TotalDurationPerdayInMinutes` END)

All comments

ST_Superman

Have you tried:

AVG ( CASE WHEN `Status` IN('break','unscheduled break') THEN `TotalDurationPerdayInMinutes` END)

John-Peddle

Thanks for the reply, @ST_-Superman-_; tried your suggestion but it seems to return the same as one we used, showing only 'BREAK and not a sum of both as we're looking for.

It would seem to be easier to get from within Domo since it's actually providing that sum in the column itself, yet it's proving to be rather difficult to figure out? Any other thoughts?

Screen Shot 2018-04-02 at 9.54.31 PM.png

ST_Superman

Have you tried using a stacked bar instead? I’ll have to think some more about the nested bar summary number, but I think that a stacked bar would work.

John-Peddle

Good morning, @ST_-Superman-_. Just tried the stacked bar and grouped bar, along with each available measure and the beast modes that have validated but it still just provides the average 'BREAK' time only as it has before, and not a sum of the two.

If you have any other suggestions or thoughts on this, please let us know! Wondering if we should also kick this up to Domo Support since what we're looking for is visible in the grouped bar total, but getting it to the summary number seems to be quite difficult. Thanks!

ST_Superman

Are you always wanting to look at the current month?

I think that the date field is what is throwing off your summary number, because you want the summary number to only be the current month.

try this:

CASE WHEN YEAR(`Date Field`)=YEAR(CURDATE()) AND MONTH(`Date Field`) = MONTH(CURDATE()) THEN

AVG(CASE WHEN `Status` = 'Break' THEN `TotalDurationPerdayInMinutes` END) + AVG(CASE WHEN `Status` = 'Unscheduled Break' THEN `TotalDurationPerdayInMinutes` END)

END

And then uncheck the "Use current Value" box on the summary number to use all values.

ST_Superman

If that works... I think I would recommend changing it to a rolling 30 day window. Something like this:

CASE WHEN `Date Field` >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN

AVG(CASE WHEN `Status` = 'Break' THEN `TotalDurationPerdayInMinutes` END) + AVG(CASE WHEN `Status` = 'Unscheduled Break' THEN `TotalDurationPerdayInMinutes` END)

END

John-Peddle

Yes @ST_-Superman-_, always lookin to display the current month for the top level on this card. Sadly, this returns the same "BREAK" only current average when set to USE CURRENT VALUES, and when changing to USE ALL VALUES it zero's out completely.

I'd really think that this should be soooooo much easier since it's already available, strange that it would be so difficult to use that sum total for the summary number?!? I appreciate all your help on this; at this point wondering if it's just something that can't be done as we'd like? Smiley Sad

ST_Superman

Don't give up until you contact Domo support. Those guys are awesome. Sorry I couldn't help

John-Peddle

@ST_-Superman-_thanks for all your help, as always, it's greatly appreciated! I'll leave this open for now, and double back to post if Domo Support is able to provide a solution for this. Smiley Happy

Taking @ST_-Superman-_'s example and just inverting it a little bit, what about:

AVG(CASE WHEN YEAR(`Date Field`)=YEAR(CURDATE()) AND MONTH(`Date Field`) = MONTH(CURDATE()) AND `Status` = 'Break' THEN `TotalDurationPerdayInMinutes` END)

AVG(CASE WHEN YEAR(`Date Field`)=YEAR(CURDATE()) AND MONTH(`Date Field`) = MONTH(CURDATE()) AND `Status` = 'Unscheduled Break' THEN `TotalDurationPerdayInMinutes` END)

Does that work?

John-Peddle

Smiley Happy @AS, thanks for taking the time to provide beast mode, Aaron; unfortunately we're seeing the same behavior as before, depending on what were setting the Summary Number to:

USE CURRENT VALUE again shows 'BREAK' average only, and USE ALL VALUES displays 0.00.

Though it wasn't related to a Summary Number, seem to recall a while back that we had an issue with averages that provided quite a challenge and we could only get an "average of averages", not a sum of two averages (unfortunately, pretty sure we gave up on it at that time).

I'm submitting a Domo Support ticket to see what they say, hoping they might be able to resolve or provide a work-around which I'll defintely post here. @ST_-Superman-_ & @AS - thank you both for your time, we appreciate you guys sharing your knowledge that helps us to continue learning from everyone here!

ST_Superman

In that case, since we are only looking at two values here couldn't you take the average of these two averages and then multiply by 2?

John-Peddle

@ST_-Superman-_, tried what you said but that returned a couple different results based on the beast mode used, neither of which was correct.

The first was using one of the beast modes you provided earlier, and just making a quick update to multiply by 2; here's what we get:

Screen Shot 2018-04-03 at 2.39.57 PM.png

We also attempted to use something more simplified, trying AVG(`TotalDurationPerdayInMinutes`) * 2 and this is what the Summary Number returned when using that:

Screen Shot 2018-04-03 at 2.43.58 PM.png

JacobFolsom

@John-Peddle,

I have a solution for you using Window Functions in beastmode. This is not a documented product feature and as-of today is to be used at your own caution as it comes with some limitations, but it does work well in some scenarios.

NOTE: This aggregates the daily data up to the MONTH in the PARTITION clause. Then by using the Current = Last feature in the Summary Number, it grabs the value of the latest date in the graph, which has been pre-aggregated to the monthly total by the beastmode already. CAUTION: If you don't use this properly, it can lead to overstated numbers, since it generates a monthly total for each date.

Below are two solutions using this approach on Nested Bar Charts viewing (1) Sum of Sums and (2) Sum of Average.

1) Monthly SUM of SUMs

Beastmode:
SUM(SUM(`Order Amount`)) OVER (PARTITION BY MONTH(`Order Date`))

2) Monthly SUM of Averages

Summing averages is trickier, since it requires two nested calculations. We can't have two window functions nested in the same statement, but since we can get the monthly average calculated in a window function, we can simply add these averages together instead of using the SUM function, similar to the thought process demonstrated earlier in this thread.

-- Get the average order amount of each country by month and add together
AVG(AVG(CASE WHEN `Country` = 'US' THEN `Order Amount` END)) OVER (PARTITION BY MONTH(`Order Date`)) +
AVG(AVG(CASE WHEN `Country` = 'JP' THEN `Order Amount` END)) OVER (PARTITION BY MONTH(`Order Date`)) +
AVG(AVG(CASE WHEN `Country` = 'CA' THEN `Order Amount` END)) OVER (PARTITION BY MONTH(`Order Date`))

-- Get the average order amount of each country by month and add together
AVG(SUM(CASE WHEN `Country` = 'US' THEN `Order Amount` END)) OVER (PARTITION BY MONTH(`Order Date`)) +
AVG(SUM(CASE WHEN `Country` = 'JP' THEN `Order Amount` END)) OVER (PARTITION BY MONTH(`Order Date`)) +
AVG(SUM(CASE WHEN `Country` = 'CA' THEN `Order Amount` END)) OVER (PARTITION BY MONTH(`Order Date`))

ST_Superman

Thanks for sharing @DataJake

Have we always been able to use windowed funtions with beastmodes or is that a recent addition? I don't remember that function being available in the past.

At any rate, glad to hear that it is an option now!

John-Peddle

Yeah, thanks for sharing that @DataJake, sorry for not repying sooner but wasn't able to take a look at this before now. We'll try and revise our beast mode today or later tonight and post here to confirm it works. Again, thanks for taking the time to share this, really appreciate it!

John-Peddle

Jacob (@DataJake), thanks again for your assistance. If we're utilizing the beast mode you provided correctly for our instance (see screenshot), it's still not working for us as it is in your card.

Not sure what the issue is, but everyones help is greatly appreciated and we're forwarding this info on to our Engineering Team, see if they're able to get it to work as it should. Have a great day!

Screen Shot 2018-04-18 at 11.58.36 AM.png

JacobFolsom

@John-Peddle

I can't see anything wrong myself. Is "Current" in your Summary Number set to Last?

It may be that the 29.94 is the First..

What happens in the Summary Number if you only have the line for Unscheduled Breaks instead of adding them together...will it show 8?

John-Peddle

@DataJake, sorry for the delayed response but we've been working on another project that took up a lot of time! And again, thanks for all your assistance (as well as everyon else), it's greatly appreciated!

Just wanted to respond to your last inquiry in case you see anything in this (screenshots for each):

1) yes, we have "Current" Summary Number set to LAST and

Screen Shot 2018-05-04 at 4.45.37 PM.png

2) when updating the STATUS filter to only Unscheduled Break, we're not seeing any break data in the Summary Number; could very well be where the issue lies, no?

Screen Shot 2018-05-04 at 4.46.11 PM.png

John-Peddle

[Deleted User]

@John-Peddle

Thank you for closing the loop on this and sharing the solution from Domo Support!

Regards,