-
1. Re: Filter values up till current month
Simon RuncSep 1, 2015 8:01 AM (in response to Randy Allan)
hi Randy,
Yes we can certainly help with this, but a couple of Qs!
Firstly can you confirm you are using Tableau 9.0? and also are you using Today() as a work around to get the latest date? (i.e. would you rather drive this from the last date in the data...just thinking of when data loads fail...etc.)
Do you also need to pick up the current year? or do you already have this set in a filter (or irrelevant as you only ever have current year in your data)
If you let me know on the above, I can show you how to do this.
-
2. Re: Filter values up till current month
Randy Allan Sep 1, 2015 8:18 AM (in response to Simon Runc)Hi thanks for the response. Yep, Tableau 9.0. Currently just using TODAY() as a workaround to get the latest date. I have data from January 2014 to December 2015. I want to apply a filter to my graph that only shows data from January 2014 to whatever the previous month of the current date. Today would be September 1st so I would want to return data up until August 2015.
-
3. Re: Filter values up till current month
Rody Zakovich Sep 1, 2015 8:28 AM (in response to Randy Allan)1 of 1 people found this helpfulHello Randy,
Have your tried something like this?
[You Date] <= DATEADD('day', -1, DATETRUNC('month', TODAY()))
the second part of this Calc will always return the Last Day of the Previous Month.
So this calc would limit your dates to only return data up until the Last Day of the Previous Month.
Regards,
Rody
-
4. Re: Filter values up till current month
Rody Zakovich Sep 1, 2015 8:32 AM (in response to Rody Zakovich)2 of 2 people found this helpfulSorry this can be made a lot simpler.
[You Date] < DATETRUNC('month', TODAY())
Regards,
Rody
-
5. Re: Filter values up till current month
Simon RuncSep 1, 2015 8:54 AM (in response to Randy Allan)
1 of 1 people found this helpfulhi Randy,
My solution is a variation on Rody's, however I'm using LoDs so we filter out to the last completed month. This might not be your exact final solution, but hopefully you can adapt the logic to your exact case.
btw in the attached example I've filtered the superstore to 2014, to mimic your case, but you could equally use this method to filter to Max year too.
So first I use a LoD calculation to put against every row, the Last Month in the data. This is called 'Max Month' and is
{MAX(DATEPART('month',[Order Date]))}
The datepart bit returns the integer of the last month in the data (12 in this case, as the last date is 31/12/2014)
I can then, like Rody's, use this to filter out the months I don't want. In my example the field is called 'Filter on Last Full Month' and is
DATEPART('month',[Order Date]) < [Max Month]
(the
Hopefully you can see what's going on in the 'How it works' tab, where I've detailed out how the calc, works, and then in the next tab I've show it in action!
If this doesn't make sense, or doesn't solve your issue please post back
-
6. Re: Filter values up till current month
Cameron Sutcliff Dec 9, 2015 7:44 AM (in response to Simon Runc)Hey Randy!
I have a similar question filtering out the last month, though my structure is a little different...
We have a filter to show the last X Months/Quarters/Years of our records, but I'd like to just exclude the penultimate month in our calculations as a data refresh on 12/9/15 doesn't include all of Dec, 2015's data.
Any suggestions?
-
7. Re: Filter values up till current month
Saurabh Singhal Dec 9, 2015 8:14 AM (in response to Cameron Sutcliff)Hi Cameron,
If I understand correctly, in your calculations you do not want to include Dec, 15 as part of last 13 months. If that is so, then using Relative Date filter may not help as it will be anchored to current system time/date by default. For such scenario, LoD expressions should help where you can define the latest date as Max(DB Date) - the way Simon has explained in previous post.
-
8. Re: Filter values up till current month
Simon RuncDec 9, 2015 9:55 AM (in response to Cameron Sutcliff)
1 of 1 people found this helpfulHi Cameron,
If you want to use the Relative Date filter, and not have it anchored to today you might find this useful
Base Relative Date Filter on Latest Date in Data
You'll need to use with care (read the comments from Pooja and Rody) as you can't control the DATETRUNC part of the relative date filter (eg. if you viz is at Week Level, and your user uses last X months).
Personally, I'd create a Month Index
DATEDIFF('month',[date],{MAX([Date])})*-1
This means all you months are dynamically indexed from 0 backwards (so 0, -1, -2, -3...) with 0 being the current month. You can then set up a parameter letting the user select how many months back they can see (filtering out the Monthindex = 0) and adjusting your filter formula to account for this. The only draw back is on the last day of the month, there is potentially all that months data to view, but as this would be MonthIndex = 0, people wouldn't see it until you got data in for the 1st of the following month. So 1 day out of 30 its not perfect (and allowing that this will sometimes fall on a weekend...etc.) its a cost I'm happy with.
You could even, using the clever parameter naming convention, I've commented on here, https://community.tableau.com/docs/DOC-6205#comment-13722 create a second parameter so the user could select week, month...etc. and use this in your Periodindex...so DATEDIFF([parameter.period_selector],[date],{MAX([Date])})*-1. This way your user would have the same functionality (basically re-creating the relative date filter with 2 parameters), but you control how its handled (Tableau calculation wise).
....just a thought
-
9. Re: Filter values up till current month
Rajeev Pandey Dec 9, 2015 12:12 PM (in response to Simon Runc)Simon Runc : This is supercool.I will Definitely use this technique