Concat Summary # Issues

user065519
user065519
in Dataflows

I'm having challenges getting my custom summary # to populate correctly. 

Below is my calculation, removed secure info with xxxxx.

The formula is valid, but I'm just getting a result of 0 on my summary # without text or calculation.

What am I doing wrong? 

 

 

CONCAT(avg (CASE WHEN `Offering Class Number` = 'xxxxxx' AND `Dim - CalendarFiscal Week` < '2021-W09' THEN `TTC_NUM`/`TTC_DENOM`
end)
+
(CASE WHEN `Offering Class Number` = 'xxxxxx' AND `Dim - CalendarFiscal Week` <'2021-W12' THEN `TTC_NUM`/`TTC_DENOM`
END)
+
avg(CASE WHEN `Offering Class Number` = 'xxxxxx' AND `Dim - CalendarFiscal Week` <'2021-W21' THEN `TTC_NUM`/`TTC_DENOM` /3 END), ' Avg Before Training | ',
CONCAT (avg (CASE WHEN `Offering Class Number` = 'xxxxxx' AND `Dim - CalendarFiscal Week` >= '2021-W09' THEN `TTC_NUM`/`TTC_DENOM`
end)
+
avg(CASE WHEN `Offering Class Number` = 'xxxxxx' AND `Dim - CalendarFiscal Week` >= '2021-W12' THEN `TTC_NUM`/`TTC_DENOM`
END)
+
avg(CASE WHEN `Offering Class Number` = 'xxxxxx' AND `Dim - CalendarFiscal Week` >= '2021-W21' THEN `TTC_NUM`/`TTC_DENOM`
/3 END), ' Avg After Training'))

 

Tagged:

Comments

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    It might make sense to break your beast mode into it's composite parts, and then put each metric in a separate beast mode and drop it onto a table card without any other columns on the axis.

     

    in order for your summary number to work, you can't have any NULLs  because CONCAT( <arg1>, NULL, <Arg2>) will return NULL.

     

    Also <arg1> + <arg2> + null NULL will return NULL.

     

    Lastly, I would be really wary of hardcoding dates into my calculation.  ex Dim - CalendarFiscal Week` >= '2021-W12' 

    This makes a nightmare to maintain.  Instead, I would alter the date dimension to have a flag to differentiate 'before XYZ point in time'.  That way you can alter the contents of the flag column without having to alter the beast mode. 

     

    Ex. CASE WHEN isBeforeTraining = 1 is a cleaner case statement than a date comparison.

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories