Concat Summary # Issues

user065519

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'))

 

jaeW_at_Onyx

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.