Beast Mode SUM Aggregation Workaround

JYang
JYang Member
in Archive

I'm trying to structure a basic formula in Beastmode to calculate the total sum of a sales figure within a time range and then apply a multiplcation ONLY if it's positive.

 

Dataset example (daily sales forecast):

1/1/2016 - $10

1/2/2016 - $-30

1/3/2016 - $40

 

Beast mode example:

WHEN SUM(Sales forecast) > 0 then SUM(Sales forecast)*2

else

0

END

 

However, what's happening is that the beast mode is applying this formula for each individual daily entry.

 

E.g. The entire sum of the sales forecast for January 2016 is -$100. When applying the above beast mode, it SHOULD be 0. However, it's still returning a positive number (e.g. $400) because it's applying the logic individually to each daily value (e.g. if 1/1/2016 is <0 then 0, else *2).

 

Is there any workaround for the beast mode to be dynamic in this way?

 

Regards,

 

 

Comments

  • PodiumMason
    PodiumMason Contributor

    Hey JYang,

     

    I did some testing to see if I could find a way to get around the row by row functionality of beast mode, and I definitely see the issue with what you're trying, and I unfortunately wasn't able to find a way to do what you need in a beast mode. 

     

    This may not be what you want to hear, but I would create a dataset in an ETL or Dataflow that rolls the data up by year-month. That way you can use your case statement beast mode with an already aggregated value as opposed to trying to aggregate the data multiple times in beast mode. 

     

    Best of luck!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • AS
    AS Coach

    Mason's correct, comparing aggregations to individual values doesn't usually work.  I've see aggregations in case statements work, but only isolated incidents and never comparing against row values.

    Just a shot in the dark here, but can you try aggregating each side of your comparisons in your logic, so everything is an aggregation?

     

    WHEN SUM(Sales forecast) > SUM(0)

    then SUM(Sales forecast)*AVG(2)

    else SUM(0)

    END

     

    Mathematically that should be the same.  Let us know if that works.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • JYang
    JYang Member

    Hey all,

     

    Thanks for the input. Unfortunately, the suggested solution doesn't work as well.

     

    The point about creating monthly summaries is a good workaround, but it makes it limited to 'static' aggregates (e.g. monthly, weekly, etc). Whereas what I had in mind was a more dynamic solution (e.g. any customizable time range).

     

    Oh well. This may just end up being a manual effort instead of automated.

     

    Cheers,

  • acgarcia21
    acgarcia21 Member

    The SUM has to encompass the formula. Should look like this:

     

    SUM(CASE when 'field' < 0 then 'field' else 'field' * 2 end)

Categories