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: https://domo-support.domo.com/s/article/4408174643607?language=en_US

  • 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 (BY SP_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 (BY SP_Fiscal_Calendar.Fiscal_Year,lte_donation_general.collection_date_dayofweek_tz, lte_tbl_calendar.calendar_name_tzw))