Beast Mode to add two values from the same column

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.

Answers

  • GrantSmith
    GrantSmith Indiana 🥷

    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)
    


    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    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.

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

    @MarkSnodgrass - First! 😂



    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

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

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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.


  • 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
    MarkSnodgrass Portland, Oregon 🥷

    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
    
    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • @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.


  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    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.

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • @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
    Ramon_Romero México ⚪️

    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