Beast Mode SUM Aggregation Workaround

JYang

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,

 

 

PodiumMason

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!

AS

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.

JYang

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

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

 

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