How to subtract MAX values between consecutive months

djaenike
djaenike Member
in Beast Mode

Hello,

I'm looking for a way to subtract the MAX values between consecutive months across a 12 month period, then display that difference for each month. Currently I am subtracting the MAX and MIN values for each month which is working as expected. Here is a snapshot of the card I'm using:

image.png
image.png

Here are the beast mode calculations I am currently using (doing some workaround to convert string to numeric, hence the "Replace" portion of the calculations):

image.png
image.png
image.png

What I need to figure out is how to reformat the BOM calculation to capture the MAX value from the previous month.

Is this possible to do within a beast mode calculation?

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited September 2024

    MAX(CASE WHEN all of your other logic AND yourDateField <= LAST_DAY(CURRENT_DATE()) - INTERVAL 1 MONTH AND yourDateField >= LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 2 MONTH THEN your other logic END

    If I solved your problem, please select "yes" above

  • djaenike
    djaenike Member

    I removed the last line of date logic - was only giving me a value for August. Using the new calculation below I was able to get some values, however it's just reporting the MAX for the current month, not the previous.

    image.png
    image.png

    Odd - I would think subtracting a month from CURDATE would give me the previous month MAX

Categories