Beast Mode Formula is not working as expected

jtrollinger
jtrollinger Member
in Beast Mode
image.png

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

Best Answer

  • jtrollinger
    jtrollinger Member
    Answer ✓

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

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jtrollinger
    jtrollinger Member

    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
    MarkSnodgrass Coach

    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.

    image.png

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jtrollinger
    jtrollinger Member

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

    image.png

  • MarkSnodgrass
    MarkSnodgrass Coach

    Try editing your beast mode to look like this:

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

    /

    SUM(IFNULL(ProjTotalCostEOJ 1,0))

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jtrollinger
    jtrollinger Member

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

  • jtrollinger
    jtrollinger Member
    image.png

  • jtrollinger
    jtrollinger Member
    image.png

    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
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • marcel_luthi
    marcel_luthi Coach

    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)

    image.png


    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
    jtrollinger Member
    Answer ✓

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

Categories