14 Replies Latest reply on Feb 22, 2019 8:22 AM by Shinichiro Murakami

Select year and previous year based on same filter

Amit Darak Feb 10, 2019 9:07 PM

Hi ,

I want to select value based on a filter.

As seen in the dashboard attached, when I select a year in filter I need to see data month wise and its grand total which is already done.

Also when I have selected a filter I need to see consolidated values only for the previous year which will be in another sheet. and then I will combine 2 sheets as already done in dashboard.

For eg if select year 2018 in filter, I need to see 2018 data month wise and only consolidated values of 2017. Similarly if I select 2019 in filter, I need to see 2019 data month wise and only consolidated values of 2018 and so on.

I have created another sheet for previous year. But don't know how to achieve what I want to.

Any help!!

Year vs previous year.twbx 2.3 MB

1. Re: Select year and previous year based on same filter

Shinichiro Murakami Feb 10, 2019 9:44 PM (in response to Amit Darak)

Hi Amit,

Normal filter filters underlying data and you cannot show previous year once you filter a certain year.
There is workaround but very different from the version.
Unless you don't have 2018.3, it's quite a bit troublesome. What is your version?

Shin
Like Show 0 Likes(0)

Actions
2. Re: Select year and previous year based on same filter

Amit Darak Feb 10, 2019 9:54 PM (in response to Shinichiro Murakami)

Hi Shin,

I have Tableau 2018.2.
Like Show 0 Likes(0)

Actions
3. Re: Select year and previous year based on same filter

tian.yang.4 Feb 10, 2019 10:15 PM (in response to Amit Darak)
Hi Amit,

Please see attached workbook.

If anything unclear please let me know.

If this posts assists in resolving the question, please mark it helpful or as the ' correct answer' if it resolves the question.

YT

selected year VS previous.twbx 2.3 MB
Like Show 0 Likes(0)

Actions
4. Re: Select year and previous year based on same filter

Shinichiro Murakami Feb 10, 2019 10:18 PM (in response to Amit Darak)

If the version is 2018.2, Tian's answer with using Parameter instead of filter should be the best way.

Shin
Like Show 0 Likes(0)

Actions
5. Re: Select year and previous year based on same filter

tian.yang.4 Feb 10, 2019 10:20 PM (in response to Amit Darak)
Hi

Here is the screenshot.

And attached file is for 2018.2

selected year VS previous_v2018.2.twbx 2.3 MB
Like Show 0 Likes(0)

Actions
6. Re: Select year and previous year based on same filter

megha gawali Feb 11, 2019 1:16 AM (in response to Amit Darak)
Hello Amit,
You can try out this.

Current Year filter
[Year]=DATEPART('year', [Order Date])

Previous year filter
YEAR([Order Date])=[Year]-1

selected year VS previous_v2018.2.twbx 2.4 MB
Like Show 1 Likes(1)

Actions
7. Re: Select year and previous year based on same filter

michael.lowden Feb 11, 2019 7:45 AM (in response to Amit Darak)

I agree with the previous posts regarding a Parameter instead of a basic filter.

For people that don't want to download the TWBs:
1. Create your [Year] parameter : I'd suggest an Integer Range (formatted without decimals or commas), 2000-2040 or something so you don't have to bother with it for a long time. Or just allow people to enter whatever they'd like manually using "Allowable Values: ALL"
2. Create a calculated field ("Previous Year") : [Parameters].[Year]-1
--- Make this field a Dimension (just drag it from Measures to Dimensions)
3. Create filters as needed against the appropriate value. Either [Parameters].[Year], or the [Previous Year] field created above
Like Show 0 Likes(0)

Actions
8. Re: Select year and previous year based on same filter

michael.lowden Feb 11, 2019 7:47 AM (in response to michael.lowden)

another bonus of Parameters is that they are carried across all sheets / pages / etc. within your dashboard. So it's an 'enter once and forget it' kind of scenario where Filters can be per-sheet and get easily lost in the weeds of a dashboard, or duplicated for no reason. You could also have two Parameters "Select Primary Year" and "Selected Comparison Year" so users could choose 2019-vs-2015 if they wanted.
Like Show 0 Likes(0)

Actions
9. Re: Select year and previous year based on same filter

Amit Darak Feb 18, 2019 8:59 PM (in response to tian.yang.4)

Hi Tian,

Thanks for the solution. It worked for me.
Like Show 0 Likes(0)

Actions
10. Re: Select year and previous year based on same filter

Amit Darak Feb 18, 2019 9:01 PM (in response to megha gawali)

Hi Megha,

Even this worked correctly. Thanks
Like Show 0 Likes(0)

Actions
11. Re: Select year and previous year based on same filter

Amit Darak Feb 22, 2019 7:11 AM (in response to tian.yang.4)

Hi Tian,

I am facing one problem with my original data.

Fiscal year for me starts from April. But when I select year 2019, it shows only data for Jan-19 and Feb 19 whereas it should have shown from April-18 to Jan-19.

I have changed default date properties and made fiscal year start date from April. Still the problem persists.

So even when I select 2018 it shows wrong values.
This is how it looks when I select 2018:

And this is how it looks when I select year 2019
Any idea how to solve this?
Like Show 0 Likes(0)

Actions
12. Re: Select year and previous year based on same filter

Shinichiro Murakami Feb 22, 2019 7:28 AM (in response to Amit Darak)

You need to convert your "Date" to fiscal calendar basis.

Thanks,
Shin
Like Show 0 Likes(0)

Actions
13. Re: Select year and previous year based on same filter

tian.yang.4 Feb 22, 2019 7:50 AM (in response to Amit Darak)

Hi Amit,

As my understanding, dates after 2018.4.1 are belong to 2019 FY.

So I use superstore as an example. (2017.4.1- 2018.4.1 2018 FY; 2018.4.1-2019.4.1 2019 FY)

Try this way, put fiscal in filters.

YT
Like Show 0 Likes(0)

Actions
14. Re: Select year and previous year based on same filter

Shinichiro Murakami Feb 22, 2019 8:22 AM (in response to Shinichiro Murakami)
2018.2 Version attached.
Shin

selected year VS previous_v2018.2.twbx 2.4 MB
Like Show 0 Likes(0)

Actions