Beast Mode to add two values from the same column

Golfoholic

I have a column that is showing different periods. the column breaks them out in 0-10,11-30, 31-60. Then there is a column called 'values' with the correlating value for those periods.

What I am wanting to do is just add together the 0-10 and 11-30 periods together using a beast mode.

GrantSmith

You can use a conditional sum like the following (assuming you're not grouping based on the description field)

SUM(CASE WHEN `Description` IN ('0_0-10_Period', '0_11-30_Period') THEN `Value` END)

MarkSnodgrass

You can create a new group name and then use this field in your visualization to do this. Your beast mode would look like this:

CASE WHEN Description IN ('0_0-10_Period','0_11-30_Period') THEN '0-30'
ELSE Description
END

You can then just drag in your value field and it will aggregate it based on what aggregation you choose.

Hope this helps.

GrantSmith

@MarkSnodgrass - First! 😂

MarkSnodgrass

@GrantSmith back to beating me by milliseconds again. 😁 You may be first, but what about better? Only @Golfoholic can answer that. 😉

Golfoholic

Ok tiebreaker! @MarkSnodgrass @GrantSmith this will move the original goal post a bit. This is trying to be used in a table. On the date filter i have it open to all time and the columns are MTD or prior month. Calculated using beast modes. How can I add the two periods above for the most recent. which would be 3/14. Using the beast mode provided is summing all time with that date filter setting.

Golfoholic

Attempted trying to do it with this beast mode, this is like the ones used for the other MTD columns here but I assume I am not getting the results I am looking for because it isn't exactly MTD it's 30 days, and adding the two together adds another layer of complexity.

SUM(CASE

  WHEN `Description` IN ('0_0-10_Period', '0_11-30_Period')

   AND MONTH(`Current_Date`)=MONTH(CURDATE())

   AND YEAR(`Current_Date`)=YEAR(CURDATE())

  then `Value` 

  END)

MarkSnodgrass

What values does your 'Current_Date' field have in it? Based off your first screenshot, I thought you would be using 'Activity_Date'. Your beast mode would get you MTD since you are looking at the month and the year. If you need all rows with an activity date in the last 30 days, you might try:

CASE WHEN DATEDIFF(Current_Date(),'Activity_Date') <=30 THEN 'Value ELSE 0 END

Golfoholic

@MarkSnodgrass shoot it should be activity date, i was toying around with it cause there is a current_date column as well. a little background on this dataset. It runs daily so we get a current_date like 3/14 and activity date that aggregates all the previous months and has a format like 2/1/2022. I am doing a bad job explaining but attached is sample data for those 2 periods and what this dataset structure looks like.

StorePerformance_Prod Sample Data.xlsx

MarkSnodgrass

Have you tried using the first beast mode that I suggested that creates a new group name and then use the use the activity date field in the date range filter? The date range filter has a lot of great options like current month, last 30 days, etc... Seems like this is all you need.

Golfoholic

@MarkSnodgrass Each column is a MTD or a prior month calc. They have been written using a custom beast mode like:

SUM(CASE

  WHEN `Description` like '%1_Move-Ins_sDescActivity%' 

   AND MONTH(`Activity_Date`)=MONTH(CURDATE())

   AND YEAR(`Activity_Date`)=YEAR(CURDATE())

  then `Value` 

  END)

so that each columns only shows the intended MTD or prior month. Using the date filter would impact prior month columns. For the intended users of this it is best to just have a column label and the coordinating values. the date filter would act as a distraction and would open a can of worms.

Ramon_Romero

i think you may use windowed functions to sume the data by diferent groups in one column, see this video below.

https://www.youtube.com/watch?v=ZPf41Fjn1H8