Beast Mode to add two values from the same column

Golfoholic
Golfoholic Member
in Charting

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.


image.png

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

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    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 Coach

    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.

    **Check out my Domo Tips & Tricks Videos

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

    @MarkSnodgrass - First! 😂

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach

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

    **Check out my Domo Tips & Tricks Videos

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

    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.

    image.png


  • Golfoholic
    Golfoholic Member

    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 Coach

    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
    **Check out my Domo Tips & Tricks Videos

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

    @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
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

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

    @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 Member

    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

Categories