I have a beast mode I'm trying to use that calculates individual performance goals. Using a table card, and including total and subtotal rows.
The first version of the beast mode was: (when sales dollars grow $2500 or more a 1% bonus is paid)
case
when (SUM(case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end)-SUM(case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end))>2500
then (SUM(case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end)-SUM(case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end))*.01
else 0
end
This give the correct result at the row level on the card but not for the subtotals and the total row. The beast mode is evaluating those subtotal and total rows on their own against the 2500 parameter instead of aggregating the individual values. So I changed the beast mode by wrapping the whole thing in a SUM() as follows:
SUM(case
when (SUM(case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end)-SUM(case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end))>2500
then (SUM(case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end)-SUM(case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end))*.01
else 0
end)
This produces the "An issue has occurred during processing. We are unable to complete the request at this time." message and the card will not render. Assuming that because the beast mode has SUM() at multiple layers its creating a conflict. So I removed the SUM() elements from within the case statements as follows:
SUM(case
when (case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end-case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end)>2500
then (case when YEAR(`Date`)=2019 then `Sales Dollars` else 0 end-case when YEAR(`Date`)=2018 then `Sales Dollars` else 0 end)*.01
else 0
end)
Problem is now that the results are not correct at the row level on the card, but the totals and subtotals are aggregating correctly (based on the incorrect rows).
What am I missing? (Forest for the trees)
