Beast mode calculation for aggregating data

Lashes · 2021-10-08T04:24:34+00:00

There was an error rendering this rich post.

Hi,

I have a numeric field called "days until due" which contains negative numbers for outstanding items from -1 to -100. I am trying to group these using a beast mode calc. but keep getting syntax errors. For example, when "days until due" is from -1 to -9 days I want it to be grouped as "1-9 days", -10 to -19 days would be "10 to 19 days". The idea is to group these and sum the value for the range and chart it.

Can anyone please suggest a beast mode calc. that works?

Cheers

Find more posts tagged with

Beast Mode

Accepted answers

amehdad

Hi @Lashes, could you please share your code and the full error message?

Here's a start:

SUM(CASE WHEN 'days until due' >= -9 AND 'days until due' <= -1 THEN '1-9 days'

...

END)

All comments

amehdad

Hi @Lashes, could you please share your code and the full error message?

Here's a start:

SUM(CASE WHEN 'days until due' >= -9 AND 'days until due' <= -1 THEN '1-9 days'

...

END)

GrantSmith

Hi @Lashes

You can use some simple math to convert your negative number to a positive one and some trickery with the FLOOR and CEILING functions to get bands of 10.

CASE WHEN `days until due` * -1 > 60 THEN
  '60+ days'
CASE WHEN `days until due` >= 60 THEN
  '-60+ days'
ELSE
  CONCAT(FLOOR(`days until due` / -10) * 10, '-', (FLOOR(`days until due` / -10) * 10 + 9), ' days')
END

To explain a bit further:

`days until due` / -10

Calculates a 10s bucket and inverses your negative number to a positive number

FLOOR(`days until due` / -10)

FLOOR returns a decimal without the decimal places (1.9 -> 1)

We then multiply it by 10 to convert back to 10s, this will give us the lower end of the bucket.

We do the same thing for the higher end of the bucket but also add 9 to it so it'll end up being 19 instead of 10 for example.

I just chose a lower threshold of -60. If you have positives you'll need to put another condition right after the first one to bucket those - just don't multiply by -1.