Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

two variables: vCurrYr and vPrevYr need equation critique and input

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

4 Replies
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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