Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a field called [Fiscal Year] it includes 2008,2009,2010 and 2011
I want to create a calculated dimension that will include only Current Year and Prior year
I think the equation will be something like
{<[Fiscal Year] ={$(vCurrYr),$(vPrevY)}>}
i also tried
{<[Fiscal Year]={">=$(#vPrevYr)<=$(#vCurrYr)"},[Fiscal Year]=,[Fiscal Quarter]=,[Fiscal Month Name]>}
but i keep getting an error when i try to use the above.
Please help
Hi, I do not the content of your to variables, but you can easily accomplish your goal by doing something similar to:
sum({$< [Fiscal Year] = {'$(=max([Fiscal Year]))', '$(=max([Fiscal Year]) - 1)'}>} 1)
Regards
thank you for your reply, i am only trying to isolate the Current Year and Prior Year in one dimension
so that i have a Newly created dimension called "YR" that consists of only the Current Year and Prior year.
if shown in a pivot the only dimension "YR" would appear as below
YR
Current YR
Prior Year
then for my numeric fields, i already have that set up.
the problem is that my fiscal year includes four years 2008,2009,2010 and 2011
but i only want to show current year and prior year based on dashboard selection.
If i use
=$(#vCurrYr)
then i get the current year, however i want current Year and Prior year in one field as a new calculated dimension.
Well if you do what Ivan said above it will only show the current year and the prior year when you use [Fiscal Year] as a dimension. I hate using expressions inside set analysis though so I would go into variable overview and make two variables:
vCurrYr=year(today())
vPrevYr=addyears(today(),-1)
I know addmonths is a function so I'm assuming addyears is as well.
Then in the expression use...
=sum({$<[Fiscal Year]={">=$(vCurrYr) <=$(vPrevYr)"}>} [Sales])
Or whatever you want to sum.
If you want three different rows, one being prevoius year, one being current year, and the other being both I would just add two more expressions like I did above except only use one of the variables for each one. Personally I avoid calculated dimensions and all costs b/c they can have serious performance issues (although that wouldn't be a problem in your case).