Beast Mode Formula is not working as expected

jtrollinger

BEAST MODE - % SPENT

(IFNULL(case when `SourceCode` = 'AP' then `TransactionAmt` end,0))

/

(IFNULL(`ProjTotalCostEOJ 1`,0))

fyi - Transaction Amount = JTD and ProjTotalCostEOJ1 is a column brought in VIA ETL

jtrollinger

Mark, Yoou got me on the right path. This is the Beast Mode that ended up working. I needed to put a MAX in front of the PROJTOTALCOST because that was an already aggregated amount via the ETL. Thanks for your help.

(SUM(IFNULL(case when SourceCode = 'AP' then TransactionAmt end,0)))

/

(MAX(IFNULL(ProjTotalCostEOJ 1,0)))

MarkSnodgrass

In your table, did you select an aggregation of sum for the Projected Cost and JTD columns? If so, you would need to incorporate SUM into your beast modes, you would need to do it outside each of your IFNULL statements.

jtrollinger

SUM for RevisedEstimatedCost and JTD were pre-selected as these are aggregated via a GROUP BY in an ETL. This being the case why do I need to add SUM in front of my IFNULL Statements? I'm confused.

MarkSnodgrass

Sorry, I should have been more clear. I was referring to your card configuration in Analyzer. In Analyzer, do you have any of those fields in your card set to Sum? Or is the aggregation set to None. In Analyzer, if you have fields set to aggregate, you need to make sure your beast modes are doing the same thing.

jtrollinger

No you were clear. Yes both of those Fields were pre-selected to SUM

MarkSnodgrass

Try editing your beast mode to look like this:

SUM(IFNULL(case when SourceCode = 'AP' then TransactionAmt end,0))

/

SUM(IFNULL(ProjTotalCostEOJ 1,0))

jtrollinger

That did not work. it gave me zero % across the board.

jtrollinger

jtrollinger

Here is the data coming across FLAT (NO PIVOT TABLE).

I would think my Beast Mode would need to look like this but this does not work either.

SUM(IFNULL(case when SourceCode = 'AP' then TransactionAmt end,0))
/
MAX(IFNULL(ProjTotalCostEOJ 1,0))

MarkSnodgrass

Doing those types of calculations in a pivot table are tricky. I would try to get it working in a regular table by breaking out the individual components of this beast mode and verifying that you are getting what you expect on each row.

marcel_luthi

As a best practice I try to create separate beast modes for the numerator and denominator, and see if those are returning the values I'd expect, so I can easily troubleshoot from there to see whether there are flaws in the logic or where are things going eerie. Since all of them go to 0, it's likely the numerator is returning 0, so either Transaction Amount is returning 0 at all times or there are no entries with values for when SourceCode = 'AP', but for that you'd need to look at your source data (you can enable the Data table panel in the card to see what is being return and include those columns to better understand what might be going on)

I see the codes on the flat data you shared do not align with the codes on the pivot version you shared first, but at first glance the Beast Mode doesn't look odd.

jtrollinger

Mark, Yoou got me on the right path. This is the Beast Mode that ended up working. I needed to put a MAX in front of the PROJTOTALCOST because that was an already aggregated amount via the ETL. Thanks for your help.

(SUM(IFNULL(case when SourceCode = 'AP' then TransactionAmt end,0)))

/

(MAX(IFNULL(ProjTotalCostEOJ 1,0)))