9 Replies Latest reply on May 16, 2013 4:02 AM by Alexander Kapellos

# Last Month selected in the Filters Shelf

Hello

Is there a solution for create a calculated field where dynamically chose the last month selected on the filters shelf?

IF Month = "Last Month Selected in the Filters Shelf " then ...

Thank you very much

Ricardo Mota

• ###### 1. Re: Last Month selected in the Filters Shelf

Ricardo,

I don't know if this is possible, but right now I don't have enough info to provide a constructive response. What will you be doing with the data once you obtain it? Will it be used in calculations, filtering, or some other purpose? Would it need to be in a string format, date format, numeric format? Is there any chance you could provide a packaged workbook with a sample data set so we have a better understanding of the data and the workbook(s) you want this to work in.

Thanks,

Andy

• ###### 2. Re: Last Month selected in the Filters Shelf

Hi Andy

It's for a calculated filter:

I have aggregated data during the several months, so I want to use the last value of the selected months:

In the example, I want to create a calculated field like this example:

IF [DATE] = "Last Month Selected" then [Calculation1]

I have achieved that with : MONTH([Date] ) = 11 but it's manual (field in excel named:32 - Mercadorias (copy) ) and I want that it choose the last month selected in the filters shelf.

Thanks

• ###### 3. Re: Last Month selected in the Filters Shelf

Ricardo,

The first thing to do is determine the maximum numerical value associated with each month (i.e.:  November = 11 and July = 7). Once that is determined, you could then perform your calculation only on the month with the maximum numerical value.

In the attached packaged workbook I have accomplished this using two calculated fields:  [Determine Maximum Month] and [There can be only one (month)]. The first determines the maximum month, the second displays important information only for the month that is the maximum month.

I hope you can use these as stepping stones to create the calculated fields you need for your purpose.

Andy

By the way, I noticed that the 32 - Mercadorias field was non-numeric. If you plan to use this information in numeric calculations, you'll want to remove the space and currency symbol -- the application can add that if you need it to.

1 of 1 people found this helpful
• ###### 4. Re: Last Month selected in the Filters Shelf

Hi Andy

It's almost that.

I want that it return the value in October for 32-Mercadorias by year. You can see it in the prtscreen.

But it's a aggregated with a non-aggregated.

This is the result that I Want

Thanks

• ###### 5. Re: Last Month selected in the Filters Shelf

Hi Ricardo,

Use SUM([32 - Mercadorias]) instead of just [32 - Mercadorias] and you should be all set for that calc.

However, your sample data did not include multiple years, while your latest post mentioned wanting the latest month by year. In that case, the Compute Using of the calculation is likely going to need to be different and will depend on the arrangement of the pills in the view.

Jonathan

• ###### 6. Re: Last Month selected in the Filters Shelf

Hi Jonathan

I can´t use SUM, because it's aggregated data: For instance:

October it's the SUM of all month's so if I use SUM it will return a bad result.

That's the problem since the beginning: Aggregated Data. That's why I just want the last month.

Thanks

• ###### 7. Re: Last Month selected in the Filters Shelf

Ricardo,

This is when having a packaged workbook with an actual sample of your data, workbook formatting, and calculated fields would be handy so folks don't have to guess what you're working with and how you have everything formatted. Is there any chance you could provide a sample packaged workbook?

Thanks,

Andy

• ###### 8. Re: Last Month selected in the Filters Shelf

@Andy - I'd started working on response while you'd written yours, here it is.

@Ricardo - I suspect that the aggregation problem you were running into is that to create the final view you were thinking that you would remove the MONTH(Date) from the view. In order for Tableau to identify the last month, the MONTH(Date) needs to be in the view. For cases like this, you'd put MONTH(Date) on the Level of Detail Shelf. With this in the view, SUM(32-Mercadorias) only returns one value. However, if you want to get really specific, in cases like this I will often use the ATTR() aggregation that returns a value if and only if there is one value for that mark, otherwise it returns a special form of Null that Tableau indicates with an asterisk *.

I mocked up an additional year's worth of data and set that up in the attached. The Compute Using is an Advanced Compute Using on Month of Date, so the calc is partitioned on Year of Date.

Jonathan

• ###### 9. Re: Last Month selected in the Filters Shelf

Hi All

Sorry to jump into this topic but I would like to know if there is a way of actually choosing the last month's data. So I have a table that includes all months but I would like to narrow it down to only one row which shows the last months data depending on the time slider I have already set.

So if the user has chosen a period from Jan 2013 to April 2013 in this view he will only have data for April

Thank you

A