March 14, 2017
Tableau Tip Tuesday: Using LODs to View the Latest, Previous and Prior Months
datediff
,
datetrunc
,
latest
,
level of detail
,
LOD
,
previous
,
prior
,
Tableau Tip Tuesday
16 comments
I'm using LOD expressions in this video along with the DATEDIFF and DATETRUNC functions. Below this video, you will see the calculations that I used if you want to copy/paste them for your own use.
Latest Month Sales
IF DATEDIFF('month',DATETRUNC('month',[Order Date]),{MAX(DATETRUNC('month',[Order Date]))})=0THEN [Sales]
END
Previous Month Sales
IF DATEDIFF('month',DATETRUNC('month',[Order Date]),{MAX(DATETRUNC('month',[Order Date]))})=1THEN [Sales]
END
Prior Month Sales
IF DATEDIFF('month',DATETRUNC('month',[Order Date]),{MAX(DATETRUNC('month',[Order Date]))})=2THEN [Sales]
END
If you want the months to be labeled…
Latest 3 Months
DATEDIFF('month',DATETRUNC('month',[Order Date]),
{MAX(DATETRUNC('month',[Order Date]))}) <= 2
Latest N Months
DATEDIFF('month',DATETRUNC('month',[Order Date]),
{MAX(DATETRUNC('month',[Order Date]))}) < [How many months?]
Subscribe to:
Post Comments
(
Atom
)
Hey Andy,
ReplyDeleteNice tip! For performance the DATETRUNC() should go outside the MAX(), here's why: Let's say there are 1M rows. With {MAX(DATETRUNC('month',date))} the DATETRUNC is computed 1M times, then the MAX is computed. Whereas with {DATETRUNC('month',MAX(date))} the MAX is computed once and the DATETRUNC is computed once.
Cheers,
Jonathan
Ah nice one Jonathan! You can probably tell the logic that went through my head. Thanks for the improvement!! I'm going to test it in sql as well.
DeleteAndy,
ReplyDeleteAgree, great post and great improvement from Jonathan.
When I've implemented this I will usually have a date selector parameter in case we don't want the latest date to be the reference data. For example, let's say the end of the month occurs on a Saturday and we come into work on a Monday and were never able to see the full month compared with the previous month. Or maybe there's a need to just change the reference month.
Steve
Yeah I'd probably do that too. Good tip!
DeleteHi Andy,
ReplyDeletethank's for your post.
Could please give me a hint how to something like last 3 month versus the corresponding three month a year ago?
(for example Dez'16/Jan'17/Feb'17 vs Dez'15/Jan'16/Feb'16)
Any help would be great.
Thank you
Cheers
Holger
Holger, in this case, each IF statement would need to also have ELSE 0 included. Then for the same month prior year, you simply duplicate the calc and change offset the months by 12.
DeleteFor example, I would change Latest Month Sales to:
IF DATEDIFF('month',DATETRUNC('month',[Order Date]),{MAX(DATETRUNC('month',[Order Date]))})=0
THEN [Sales]
ELSE 0
END
Then for last month prior year, I would create a calc that is:
IF DATEDIFF('month',DATETRUNC('month',[Order Date]),{MAX(DATETRUNC('month',[Order Date]))})=12
THEN [Sales]
ELSE 0
END
Then to compare them you create another calc that is [Last Month Sales]-[Last Month PY Sales]. If I wanted it to be % change, the formula would be SUM([Last Month Sales]-[Last Month PY Sales])/SUM([Last Month PY Sales]).
Thank you for your reply, Andy.
ReplyDeleteI will try it.
Cheers
Holger
Andy
ReplyDeleteI learned some good stuff in the post. Thanks.
Is there a reason in addition to fewer key strokes, that you used the non-scoped form of the LoD calc - {MAX([Order Date])} ? I understand that form as a shorter version of of the scoped form {FIXED : MAX([Order Date])} Does the non-scoped version run faster, or perhaps keep the character count down in the calc?
Just less typing and it seems cleaner and easier to understand for me.
DeleteHi,
DeleteThanks for the video (https://www.youtube.com/watch?v=ViD3vclgNv8)
I have been trying to get a similar calculation,
I want to calculate year over year difference of Sales for all months. I used a table calculation but that will not be accurate for current month since it calculates Sum(Sales) for all days in current month in last year and only MTD for this year. Is there a way to work around this and have a calculated fields which only calculates between Same month and Day for 2 years.
Thanks a Ton!
Regards,
Pj
Hi Pj. You'll need to use the dayofyear function instead. More info here - http://onlinehelp.tableau.com/current/pro/desktop/en-us/functions_functions_date.html
DeleteHi Andy,
ReplyDeleteThanks for the video. It is amazing. I am trying to implement this. I want to show three columns, current month, previous month and same month previous year.I have a filter for selecting Month-Year. I am getting correct values for current month, however for previous month it shows 0. I think the calculations are not taking my filter selection into account.
Is there a way to fix this? I don't want use parameter as it will not be updated.
Thanks a lot
Regards,
Tushar
If you're using FIXED LODs, then those will ignore your dimension filters unless you put them in context.
DeleteHi Andy,
ReplyDeleteWhat about finding the last 4 months from the data. We have two years of data and suppose current month is Feburary. We tried using Last() func in filter but the table level calc filter does not work on Grand Total.
That would be just like the Last 3 months example except change the number of months.
DeleteHi,
ReplyDeleteI discovered this and found easiest to understand for current month and last year same month: it works only for 1 month period
current month :
if MONTH(TODAY())=MONTH([Date]) AND YEAR(TODAY())=YEAR([Date]) THEN ([Monthly Pack sales])
END
prev year same month:
if MONTH(TODAY())=MONTH([Date]) AND YEAR(TODAY())-1=YEAR([Date]) THEN ([Monthly Pack sales])
END