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

damen · 2022-09-30T22:00:15+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

Comments

RobSomers

@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

damen

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

damen

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

damen

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

GrantSmith

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

damen

@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

RobSomers

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

damen

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

RobSomers

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

damen

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

damen

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.

RobSomers

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

MichelleH

@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

@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

damen

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

MichelleH

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