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)
Best Answers
-
@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:
We also attempted to use something more simplified, trying AVG(`TotalDurationPerdayInMinutes`) * 2 and this is what the Summary Number returned when using that:
** 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!
2 -
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)1
Answers
-
Have you tried:
AVG ( CASE WHEN `Status` IN('break','unscheduled break') THEN `TotalDurationPerdayInMinutes` END)
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
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?
0 -
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.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
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!
0 -
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.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
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
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
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?
0 -
Don't give up until you contact Domo support. Those guys are awesome. Sorry I couldn't help
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
@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.
0 -
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?
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
@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!
0 -
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?
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
@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:
We also attempted to use something more simplified, trying AVG(`TotalDurationPerdayInMinutes`) * 2 and this is what the Summary Number returned when using that:
** 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!
2 -
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`))Jacob Folsom
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2 -
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!
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
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!
0 -
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!
0 -
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?
Jacob Folsom
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
@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
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?
0 -
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)1 -
Thank you for closing the loop on this and sharing the solution from Domo Support!
Regards,
0
![[Deleted User]](https://us.v-cdn.net/6032830/uploads/defaultavatar/nZ5X977MOM3Z6.jpg)
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 731 Beast Mode
- 55 App Studio
- 40 Variables
- 682 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 35 Predict
- 14 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 122 Manage
- 119 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 107 Community Announcements
- 4.8K Archive