13 Replies Latest reply on Dec 11, 2017 1:54 PM by Abhishek Belnekar

Custom Date filter that can show last 12 months in Month and Year format in drop down

Abhishek Belnekar Dec 5, 2017 2:55 AM

Hello,

I want to create a custom filter around a datetime dimension which can show 12 months including current month in month-year format. The dimension that I have used brings only few dates based on availability of the data as below

March-2017

Jun-2017

August-2017

November-2017

What I want is it should show the missing months as well

January-2017

February-2017

April-2017

May-2017

July-2017

September-2017

October-2017

December-2017

with this I would be able to generate a blank report based on customer requirement.

Any idea around this will be helpful.

1. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

Zhouyi Zhang Dec 5, 2017 4:13 AM (in response to Abhishek Belnekar)

Hi, Abhishek

Please see my solution below

Hope this could help

ZZ
1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
2. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

Abhishek Belnekar Dec 5, 2017 5:40 AM (in response to Zhouyi Zhang)

Hello Zhouyi,

Thank you for the reply.

What I want is to get it done inside the filter. So when I choose the date for which records are not available I can show "No data"

So if you build a filter around order date on Month and Year, it will show you only 3 records. But I want all 12 months of the year.

Thank you,
Abhishek
Like Show 0 Likes(0)

Actions
3. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

Norbert Maijoor Dec 5, 2017 6:43 AM (in response to Abhishek Belnekar)
Hi Abhishek Belnekar,

Find my approach as reference below based on "Scaffolding" stored in attached workbook version 10.3 located in the original thread.

1. Scaffold is explained here

2. D1 Display: if datediff('month',[Date],today())>=0 and datediff('month',[Date],today())<=11 then [Date] END

3. Drag required objects to the indicated locations and "un-check" Show Header of D1. Display > Right Click > Text Menu > Show Header

Regards,
Norbert

Scoffold_Actual_nalmai_10.3.twbx 27.8 KB
Like Show 0 Likes(0)

Actions
4. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

Abhishek Belnekar Dec 5, 2017 1:48 PM (in response to Norbert Maijoor)

Hello Norbert,

Thanks for the reply.

What I want is this should happen inside the filter and not in crosstab. I want to have the missing values in filter.

Thanks,
Abhishek
Like Show 0 Likes(0)

Actions
5. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

Zhouyi Zhang Dec 5, 2017 5:01 PM (in response to Abhishek Belnekar)
Hi, Abhishek

Please find my sample workbook attached.

ZZ

Sample.twbx 1.2 MB
Like Show 0 Likes(0)

Actions
6. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

Abhishek Belnekar Dec 6, 2017 5:35 AM (in response to Zhouyi Zhang)

Hi Zhouyi,

I need these missing dates to appear inside the filter. I have attached the screenshot here. That's where I am trying to have the values

Thanks,
Abhishek
Like Show 0 Likes(0)

Actions
7. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

Zhouyi Zhang Dec 6, 2017 5:55 AM (in response to Abhishek Belnekar)

Have you opened my workbook and have a look?

or you could share yours?

ZZ
Like Show 0 Likes(0)

Actions
8. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

Norbert Maijoor Dec 6, 2017 6:55 AM (in response to Abhishek Belnekar)
Hi Abhishek,

Find my "updated attempt" below and stored in attached workbook version 10.3 located in the original thread

1. M1. True/False: isnull(if attr([Number of Records])=SUM([Actuals].[Number of Records]) then attr([Date]) end)

2. Define dashboard based on two sheets of which sheet Selector is Use(d) as Filter

Regards,
Norbert

Scaffold_Actual_updated_nalmai_10.3.twbx 38.3 KB
Like Show 0 Likes(0)

Actions
9. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

Abhishek Belnekar Dec 7, 2017 5:31 AM (in response to Zhouyi Zhang)

Hello Zhouyi,

Can you please post it in 10.2?
Like Show 0 Likes(0)

Actions
10. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

Abhishek Belnekar Dec 7, 2017 5:33 AM (in response to Norbert Maijoor)

Hi Norbert,

Can you please post your workbook in 10.2?

Thanks,
Abhishek
Like Show 0 Likes(0)

Actions
11. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

Norbert Maijoor Dec 7, 2017 6:04 AM (in response to Abhishek Belnekar)
Hi Abhishek,

Find attached in version 10.2:)

Hope it helps.

Regards,
Norbert

Version 10.2_nalmai_10.2.twbx 40.3 KB
Like Show 0 Likes(0)

Actions
12. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

Zhouyi Zhang Dec 7, 2017 3:42 PM (in response to Abhishek Belnekar)
Hi, Abhishek

Here you go.

ZZ

Sample_v10.2.twbx 1.2 MB
Like Show 0 Likes(0)

Actions
13. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

Abhishek Belnekar Dec 11, 2017 1:54 PM (in response to Zhouyi Zhang)

Hi Zhouyi,

For you the all the dates are coming from Order Date. Thats not what I want.

For me I am getting only 4 dates in Month - year format. But I still want to show all 12 in filter.

If that is not possible then reading the current month and Year how to show 12 month in Month-Year Format in Filter dropdown.

Thanks,
Abhishek
Like Show 0 Likes(0)

Actions

Go to original post

Tableau Community

Custom Date filter that can show last 12 months in Month and Year format in drop down

1. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

2. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

3. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

4. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

5. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

6. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

7. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

8. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

9. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

10. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

11. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

12. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

13. Re: Custom Date filter that can show last 12 months in Month and Year format in drop down

Actions

More Like This