FIXED BY Beastmode trouble shooting
Something is not working quite right and I am having a hard time figuring out what is going wrong.
We're trying to see the % of registrations by year, by day of week (sun, mon, tue, …), by hour of day, and then to be able to filter by locations.
So in ETL, for the numerator, I am grouping by Year, DayOfWeek, Hour of day, Location, and count distinct registrations. Then for the denominator it is the same with out the Hour of Day grouping. Then I join them on a concat of Year-Day-Location.
Then for the beastmode, I am doing
Max(Sum([numerator]) fixed (by year, day, hour))
/
Max(Sum([denominator]) fixed (by year, day))
Using the year 2020, Monday, 12 hour as an example, I queried our SQL database and the correct numerator/denominator should be 3845/39332 for 9.78 %. When I filter the domo card to this year, day, and hour, I get the correct numbers (SS1 attached)
However, when I remove the hour of day filter, me denominator jumps from 39332 to 548,378 (SS2 attached) So my denominator increases by 509,046.
So, my thought was that maybe there are a bunch of denominators that for some reason aren't attached to an hour of day, and so that is why it is correct when I filter to the hours. So, I made a beastmode to say "if the denominator is >=1 and the hour of day is null, then yes" but it returns no yes'.
beastmode:
MAX(SUM(lte_donation_general.donation_id_distinctcount_by_hour_by_year_tz
) FIXED (BY SP_Fiscal_Calendar.Fiscal_Year
, lte_donation_general.collection_date_dayofweek_tz
,lte_donation_general.collection_date_hour_tz
))
/
MAX(SUM(lte_donation_general.donation_id_distinctcount_by_dayofweek_tz
) FIXED (BY SP_Fiscal_Calendar.Fiscal_Year
, lte_donation_general.collection_date_dayofweek_tz
))
Answers
-
Is there any particular reason why you're using MAX(SUM() FIXED())? You also mentioned the Denominator being the same as the Numerator without the hour part, but in the last formula I see you're using 2 different aggregation columns.
More often than not, you'll use a SUM(SUM() FIXED()) approach if the goal is to get the total on a specific subset. You can find some more examples on how this function might be used in this article:
0 -
I think I found my mistake.
After the join, it looks like this
So I needed to change my beastmode to this
SUM(SUM(
lte_donation_general.donation_id_distinctcount_by_hour_by_year_tz
) FIXED (BYSP_Fiscal_Calendar.Fiscal_Year
,lte_donation_general.collection_date_dayofweek_tz
,lte_donation_general.collection_date_hour_tz
,lte_tbl_calendar.calendar_name_tzw
))
/
Sum(Max(lte_donation_general.donation_id_distinctcount_by_dayofweek_tz
) FIXED (BYSP_Fiscal_Calendar.Fiscal_Year
,lte_donation_general.collection_date_dayofweek_tz
,lte_tbl_calendar.calendar_name_tzw
))0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 293 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 96 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 705 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive