"current year, current month" and "previous year, current month" Beast Modes

damen
damen Contributor
edited September 2022 in Magic ETL

Hi,

I'm trying to create a 'current year, current month' and 'previous year, current month' beast mode.

To this point I have created a current year to date but am not sure of the syntax. I have watch multiple videos and have searched high and low through domos resources.

Any help with the syntax would be helpful

Here is the beast mode that we have that works for the 'current year to date' and previous year to date:

case when year(`Transaction Date`) = year(CURDATE()) then `amount` end

Case when Year(`Transaction Date`) = Year(curdate())-1 then `amount` end

If this helps, feel free to agree, accept or awesome it!

Comments

  • @damen Current month:

    case when YEAR('Transaction Date') = YEAR(CURDATE()) and MONTH('Transaction Date') = MONTH(CURDATE()) then 'amount' end

    Previous year, current month (assuming this means same month last year):

    case when YEAR('Transaction Date') = YEAR(CURDATE()) -1 and MONTH('Transaction Date') = MONTH(CURDATE()) then 'Amount' end

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

    **Did this solve your problem? Accept it as a solution!**

  • damen
    damen Contributor

    Here is what I was thinking the syntax should look like for the current month, current year but I cant get it to return and values and the formula shows it as a string instead of numbers

    case when month(`Transaction Date`) = MONTH(CURRENT_DATE) then `amount` end

    If this helps, feel free to agree, accept or awesome it!

  • damen
    damen Contributor

    @RobSomers Thanks again man. I didnt realize I needed the AND portion of that. much appreciated

    If this helps, feel free to agree, accept or awesome it!

  • damen
    damen Contributor
    edited September 2022

    @RobSomers Ok so I am still getting both of these functions wanting to run as string


    If this helps, feel free to agree, accept or awesome it!

  • GrantSmith
    GrantSmith Coach
    edited September 2022

    You can multiply amount by 1 to force it to be a number or change the data type in an ETL

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • damen
    damen Contributor
    edited October 2022

    @GrantSmith so I did get them to show up as a number but values are still not showing.



    **disclaimer: this is for the domo data specialist practical test

    If this helps, feel free to agree, accept or awesome it!

  • RobSomers
    RobSomers Coach
    edited October 2022

    @damen The beast mode you have is for current month, not current year. It will only have values for the current month (2022-Sep).

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

    **Did this solve your problem? Accept it as a solution!**

  • damen
    damen Contributor

    @RobSomers Yeah, my bad. I did get the current year and previous year to return values after a few tweaks to it.

    I am trying to do the last two on the list now.

    If this helps, feel free to agree, accept or awesome it!

  • @damen The beast mode I gave you that you were using and wasn't working for YTD is actually the beast mode for Current Year, Current Month. My original post also has the Previous Year, Current Month beast mode.

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

    **Did this solve your problem? Accept it as a solution!**

  • damen
    damen Contributor

    @RobSomers I'm still running into the same issue in that I'm not returning any data when I use that formula. I've tried different variations of it and nothing returns any values. the syntax does make sense tho. i just am not sure what i am doing wrong.

    If this helps, feel free to agree, accept or awesome it!

  • damen
    damen Contributor

    The first picture is what works for current year. I tried to just enter in the 'AND MONTH' portion of it and try the same format (and a few other ones) but again, no values have been returned at all.

    If this helps, feel free to agree, accept or awesome it!

  • @damen Just noticed that the beast mode you posted of what you're using, you put:

    then 'amount' end

    You're using 'amount' as a string so you're saying when the row meets that condition put in the word 'amount' and since the column is intergers, it comes in as nothing. You need to use whatever column contains your values.

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

    **Did this solve your problem? Accept it as a solution!**

  • @damen In your screenshot where you showed the "Current Year" beast mode, be sure to use the " ` " symbol around your Transaction Date and amount fields, instead of " ' ", so that Domo knows you are referring to columns.

    It should look like this, when forcing to a number as @GrantSmith suggested:

    case when YEAR(`Transaction Date`) = YEAR(CURDATE()) and MONTH(`Transaction Date`) = MONTH(CURDATE()) then `amount`*1 end
    


  • damen
    damen Contributor

    @RobSomers its def the amount column that contains the values. I'm confused. are the integers not calculated as values? The first beast mode (2nd picture) where we are only calculating current year, returns values


    If this helps, feel free to agree, accept or awesome it!

  • damen
    damen Contributor

    @MichelleH Yeah, sorry. Here are the updated views. I'm currently dealing with the 2nd picture being able to returning values and the first picture doesn't.


    If this helps, feel free to agree, accept or awesome it!

  • @damen What returns in your "Current Year, Current Month" beast mode and what are you expecting? Is it possible that there is no data yet, since today is only the fourth day of the month?