Data Viz Done Right

March 14, 2017

Tableau Tip Tuesday: Using LODs to View the Latest, Previous and Prior Months

11 comments
This week's tip comes from a question on The Information Lab's collaboration platform about returning the latest, previous and prior month values.

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]))})=0
THEN [Sales]
END

Previous Month Sales

IF DATEDIFF('month',DATETRUNC('month',[Order Date]),{MAX(DATETRUNC('month',[Order Date]))})=1
THEN [Sales]
END

Prior Month Sales

IF DATEDIFF('month',DATETRUNC('month',[Order Date]),{MAX(DATETRUNC('month',[Order Date]))})=2
THEN [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?]

11 comments :

  1. Hey Andy,

    Nice 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

    ReplyDelete
    Replies
    1. 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.

      Delete
  2. Andy,

    Agree, 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

    ReplyDelete
    Replies
    1. Yeah I'd probably do that too. Good tip!

      Delete
  3. Hi Andy,
    thank'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

    ReplyDelete
    Replies
    1. 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.

      For 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]).

      Delete
  4. Thank you for your reply, Andy.
    I will try it.

    Cheers
    Holger

    ReplyDelete
  5. Andy
    I 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?

    ReplyDelete
    Replies
    1. Just less typing and it seems cleaner and easier to understand for me.

      Delete
    2. Hi,
      Thanks 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

      Delete
    3. 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

      Delete