12 Replies Latest reply on Apr 16, 2014 8:34 AM by Dharmesh Purohit

# Need help to define logic for selecting years dynamically

Hi all,

I need help to build logic  for selecting years dynamically based on current year value.

Problem statement :

I have historical and future data for few years but I don't want to display all the historical years and future years on my chart. My requirement is based on current year I want to generate field which has -2 (historical) years from current year and +5(future) years from the current year.

Can somebody provide me some idea or example of logic how can I do this using Tableau on the fly?

Thanks

-Dharmesh

• ###### 1. Re: Need help to define logic for selecting years dynamically

You could try using the following custom field as a calculated field and filter dynamically on a parameter (which I have named [Date Selector]):

IF

([Order Date] > DATEADD('year', 5, [Date Selector])) OR

([Order Date] < DATEADD('year', -2, [Date Selector])) THEN "Hide"

ELSE "Show"

END

See the attached workbook for an example. Hope this helps! Note: I only included +/- 1 year in the workbook since the date data do not span many years.

• ###### 2. Re: Need help to define logic for selecting years dynamically

Dear Dharmesh,

I implemented a solution to the scenario you has described by using a Parameter, type date & time

Then using the date dimension, I created a filter based on formula that use the paramenter. See the formula bellow:

```YEAR(MIN([Order Date]))>=YEAR([Order Date Parameter])-1 AND YEAR(MAX([Order Date]))<=YEAR([Order Date Parameter])+1
```

See that in my formula I'm just creating a time window of one year before and one year after the selected year. This is because the data source I'm using has few years, from 2009 to 2012. In your case, the formula should be:

```YEAR(MIN([Order Date]))>=YEAR([Order Date Parameter])-2 AND YEAR(MAX([Order Date]))<=YEAR([Order Date Parameter])+5
```

See the attached workbook with the solution.

I hope this helps

Best

Ramon

• ###### 3. Re: Need help to define logic for selecting years dynamically

I want to do something similar that you have done in your example.

Here is my data structure see if you can help me out

 FY YEAR SALES FY10 2010 123 FY11 2011 128 FY12 2012 201 FY13 2013 210 FC14 2014 230 FC15 2015 212 FC16 2016 234 FC17 2017 245 FC18 2018 298 FC19 2019 276 FC20 2020 398 FC21 2021 350 FC22 2022 387

1. FY is my X-Axis , SALES is Y-Axis, YEAR field is just for sort purpose to sort FY field.

2. I am also creating CURRENT_YEAR calculated field to identify current year.

3. Now on X-Axis I only want to display current year , -2 past years from the current year and +5 future years from the current year. (e.g. In this case my current year is FC14 (2014) so I want to display FY12, FY13, FC14, FC15, FC16, FC17, and FC18 on my X-Axis).

- Dharmesh

• ###### 4. Re: Re: Need help to define logic for selecting years dynamically

Hi Dharmesh,

I implemented the solution based on the data set you have provided in your request.

Instead of calculated field, I create the parameter Current YEAR Parameter, type integer, Range of values based on your data.

The bar char is configured just as you want, and I use Year as conditional filter, using the formula:

```INT([YEAR])>=[Current YEAR Parameter]-2 AND INT([YEAR])<=[Current YEAR Parameter]+5
```

See the result in the attached workbook.

Note that the order of bars in the chart are based on the value of the dimension FY, so those values that include FC are before of those with values FY.

I hope this helps,

Best

Ramon

• ###### 5. Re: Need help to define logic for selecting years dynamically

Thank you , I really appreciate you help. This worked well.

I did some minor changes. Instead of using Current YEAR Parameter I am still using Current YEAR calculated filed. Reason I am using this is , I am calculating current year based on YEAR(NOW()) formula so every year I will get the new current year.

In Current YEAR Parameter I see current value is 2014 is hard coded and also min , max values are hard coded I am not sure how can make it dynamic but when I tried it with the Current YEAR calculated filed it worked same way.

I am using below formula :

INT([Year])>=[Current Year]-2 AND INT([Year])<=[Current Year]+4

Could you please also help me on this question as well this is also related to the same dataset Need help to build logic to selecting year field data based on user group permission using memberof() function

Again thank you for your great help

- Dharmesh

• ###### 6. Re: Need help to define logic for selecting years dynamically

Hi Dharmesh,

That is Ok. I'm glad you have a solution to your problem/question.

Best,

Ramon

• ###### 7. Re: Need help to define logic for selecting years dynamically

I am trying ISMEMBEROF() function as below :

IF ISMEMBEROF("NOT_ALLOWED_GROUP") THEN

INT([Year])>=[Current Year]-2

ELSE

INT([Year])>=[Current Year]-2 AND INT([Year])<=[Current Year]+4

END

but this doesn't allow me to write condition this way and giving me error "Cannot use Boolean type in IF expression"

Is there any other way to do this?

- Dharmesh

1 of 1 people found this helpful
• ###### 8. Re: Need help to define logic for selecting years dynamically

IF ISMEMBEROF("NOT_ALLOWED_GROUP") THEN

INT([Year])>=[Current Year]-2

ELSE

INT([Year])>=[Current Year]-2 AND INT([Year])<=[Current Year]+4

END

Why I am getting syntax error?

Thank you,

• ###### 9. Re: Re: Need help to define logic for selecting years dynamically

Hi Dharmesh,

IF sentence does not access booleans.

I'm not sure if this will work but try the following.

Drag YEAR to Filter and defined the condition by formula:

```IIF(ISMEMBEROF("NOT_ALLOWED_GROUP"), INT([YEAR])>=[Current YEAR Parameter]-2, INT([YEAR])>=[Current YEAR Parameter]-2 AND INT([YEAR])<=[Current YEAR Parameter]+4)
```

There is no error message this way, but you need to test the result publishing the workbook to Tableau Server.

I hope this helps

Best

Ramon

• ###### 10. Re: Need help to define logic for selecting years dynamically

Thank you Ramon Martinez this idea worked well.

• ###### 11. Re: Need help to define logic for selecting years dynamically

Hi Dharmesh,

I'm glad that solution works well.

Best,

Ramon

• ###### 12. Re: Need help to define logic for selecting years dynamically