Year Over Year Using Presentation Variables

Question

The user will prompt for a year.  I have set up a presentation variable to reference this prompt.  In the report I'm trying to set up a YOY filter referencing this presentation variable.  I want to show revenue results for current year and last year.  I'm was trying something like this: "Date"."Year" IN (@{var_year},@{var_year}-1).  It is not letting me do math here though without a function.  Does anyone know an easy way to do this?

Gianni Ceresa · Answer

Hi Matt,

The +1 is because the prior year is last year, so "year" = @{var_year} - 1 is the same as "year" + 1  =@{var_year} (not sure how to call that in English but it's just a transformation of the "equation" doing +1 on both sides of the = ).

Matt_S · Answer

Thank you Gianni, this method has worked well!  I am still confused by why a +1 actually brings in the prior year.

Gianni Ceresa · Answer

Hi,

In a IN() you can't do calculation, so why don't you turn it the other way?

"Date"."Year" IN (@{var_year})

OR "Date"."Year"+1 IN (@{var_year})

PS: if your "Date"."Year" isn't a number you must CAST it as INTEGER before to do the +1