I am needing help to create a condition in order to make a beast mode I am working on

Aaron_Polzin_EXT

I need this beast mode to switch its year offset based on which quarter it currently is. This is the first and most important part of the beast mode since it will effect how the data is read and written each quarter. I have come up with three ways to do this but none work, here is what I have come up with:

(CASE 
 	When (CASE QUARTER(SYSDATE()) =1
	THEN
  	`Year Offset` =-1 AND `Quarter Offset` =-1 then `Net Sub Contract amount USD` END
	ELSE
  	`Year Offset` =0 AND `Quarter Offset` =-1 THEN `Net Sub Contract amount USD` END
END))

CASE
	WHEN QUARTER(SYSDATE()) = 1
	THEN
  	`Year Offset` = -1
	ELSE
  	`Year Offset` = 0
	END

CASE
	WHEN RIGHT(`Quarter ID`,1)-0 = '1'
	THEN
  	`Year Offset` = -1
	ELSE
  	`Year Offset` = 0
END

They are pretty simple I think and I was hoping to get them to work, but each time I try I get a Calculated Field syntax error, any help would be appreciated!

RobSomers

@Aaron_Polzin_EXT If the column/beast mode you want to create is 'Year Offset' then it looks like the second one would work. You would just need to eliminate the 'Year Offset' portion of the formula:

case when QUARTER(SYSDATE()) = 1 then -1 else 0 end

Or if the 'Year Offset' column already exists in your data, then you would need to give your beast mode a different name like 'Year Offset New' and use the same formula above. As far as I know, you can't change columns that already exist using beast modes.

Aaron_Polzin_EXT

@RobSomers The beast mode is needing to effect the Year Offset based on what quarter of the year it is currently since part of the card I am making changes and uses data for only that quarter. It is to show the previous quarter, I have two different calculations I am using but they only work depending on the time of year, one only works during the first while the other works during the rest of the year.

RobSomers

@Aaron_Polzin_EXT If 'Year Offset' already exists and you want to use that value and substract from it in your new column, then your beast mode would be:

case when QUARTER(SYSDATE()) = 1 then 'Year Offset' - 1 else 'Year Offset' end

Is 'Year Offset' a column that is already present in your data?

Aaron_Polzin_EXT

@RobSomers Yes it is, also this works, so hopefully now I can get the entire thing to work, thank you. Will comment on whether it works or not.

Aaron_Polzin_EXT

@RobSomers okay, so we have a Quarter Offset and a Year Offset, the quarter offset is always going to be -1, while the year will fluctuate between 0, -1, and -2 I believe based on what quarter it currently is, while that calculation works on it's own, it does not work once added to the rest. Here is what it needs to switch between:

(CASE when `Year Offset` = -1 and `Quarter Offset` <= -1 then `Standard Subscription Count`
 else 0 
 END)

(CASE when `Year Offset` = 0 and `Quarter Offset` <= -1 then `Standard Subscription Count`
 else 0 
 END)

No matter what the Quarter Offset is at -1 since it is displaying information for the previous Quarter, nut the year needs to fluctuate. While the calculation you provide seems to work, now I am needing to get it to work with this.

RobSomers

@Aaron_Polzin_EXT I think it would be helpful to know what the bigger picture is of what you're trying to accomplish. Could you provide a sample of your data with the relevant columns you'll be using, and what end result you would expect for the various 'Year Offset' and 'Quarter Offset' scenarios? This will help with figuring out how to combine the case when statements.

DashboardDude

Hi @Aaron_Polzin_EXT ,

Just so I'm clear, you want the year to change based on the current quarter? Because you could do something like this

CASE

WHEN QUARTER(Sysdate())=QUARTER(Cur_Date()) AND YEAR(Sysdate())=YEAR(Cur_Date())

THEN X ElSE Y End

Just wondering if I'm understanding it correctly.

John Le

You're only one dashboard away. 

More video solutions at: https://www.dashboarddudes.com/pantry

Aaron_Polzin_EXT

@RobSomers Not really, everything being used is already being shown. The bigger picture is that I am trying to fix a dashboard that is set up to show data from the Previous Quarter, the QtD portions of it work fine, but the YtD functions do not. Those two recent examples are calculations I am currently using and that also work, but the first one only works during the first quarter of the year, while the other only works during every other quarter. I am needing this to switch between the two so it is always showing the current and correct info from either our subscriptions or billings based on what Quarter of the year it currently is. Everything is set up I believe, just need a calculation to actually make it work.

Aaron_Polzin_EXT

@DashboardDude I want the years offset to change, since it needs to be based on the quarter being shown also. I am hoping the explanation above is enough to give you two and anyone else reading this a better idea.