11 Replies Latest reply on Feb 15, 2020 7:53 AM by Matt DiPippa

# Comparing Multiple R12 Based on Single Date (Today)

Hello,

I am relatively new to Tableau and am unable to share my data set, but i am thinking this can be answered using Superstore data..

I am trying to calculate CAGR over five rolling 12 month periods based on today, so this needs to be dynamic.

I am struggling with how to setup the R12 "buckets" data if you will. For example, I want the most recent R12 data (period 1) to be today +364 days. The second most recent (period 2), days 366-731, and so on and so forth to period 5.

Is there some sort of LoD calc that can get me [measure] data for these five periods? I think there is significant Date logic that needs to be distilled that is holding me back.

Anyone up for the challenge?

Date Column Name: Order Date

Measure: Sales

Thanks,

MD

• ###### 1. Re: Comparing Multiple R12 Based on Single Date (Today)

It will need table calcs, and (especially if you are new to Tableau) it's far easier to show you in an actual workbook that you upload here than to try to describe how to do it.

• ###### 2. Re: Comparing Multiple R12 Based on Single Date (Today)

Hey Matt,

Please find attached a workbook with all the date ranges you will prolly ever need.

//Rohit

• ###### 3. Re: Comparing Multiple R12 Based on Single Date (Today)

Hi Joe,

Thanks for stepping in here. Attached you will find a mock book. To throw a curveball, if it is mid month (like today), I would like the first R12 period to start as of 1/31/2020. So Period one would be 2/1/19-1/31/20. Period two 2/1/18-1/31/19. Etc etc for 5 periods. For the sake of clarity, once "today" is March 2020, all of those R12 periods would revert to 3/1-2/29(or 2/28).

I plan on show the difference AND the actual sales values in the "table" between R12 periods.

I hope this helps. Thanks!!

MD

• ###### 4. Re: Comparing Multiple R12 Based on Single Date (Today)

Hi Rohit,

This is fantastic. However, I would need to embed these dimensional calculations into measure calcs which is a bit over my head at the moment. I replied to an earlier poster with a mock book. I will be sure to use this date logic in the future though.

Thanks!

MD

• ###### 5. Re: Comparing Multiple R12 Based on Single Date (Today)

Let me make sure I understand your requirement.

You will base this on TODAY().  But you actually want the end of the first period (looking backward) to be the last day of the prior month.  Since today is 2/14, you want the period to be 12 full months ending with January.  (And if today were April 4, the end of the first period would be the end of March. Etc.)

• ###### 6. Re: Comparing Multiple R12 Based on Single Date (Today)

Hey Matt,

Please find the workbook attached with the R12 periods that you want. The periods are based of today's date.

Let me know if this will suffice.

//Rohit

• ###### 7. Re: Comparing Multiple R12 Based on Single Date (Today)

Hi Joe,

Yeah you have it right. It is based on today, but the visual will display the most current (and all really) Rolling 12 periods as of the prior month end. So, in this case, five R12 periods February - January. Once March 2020 rolls around, it will be five r12 periods March-Feb.

I assume there are going to be multiple logic statements that come of this solution.

Thanks!

MD

• ###### 8. Re: Comparing Multiple R12 Based on Single Date (Today)

Wow. Rohit I looked at your calcs, especially the one used in the filter and they are very complex!  I will use this to implement in my actual workbook and will report back with my findings. Truly fantastic! I will mark as answered at that time too.

Appreciate it!

MD

• ###### 9. Re: Comparing Multiple R12 Based on Single Date (Today)

Nice job Rohit.

I was making my R12 calc like this:

IF DATETRUNC('month', [Order Date]) >= DATEADD('month', -12,(DATETRUNC('month',TODAY()) ) )
and DATETRUNC('month', [Order Date]) <= DATEADD('month', -1,(DATETRUNC('month',TODAY()) ) )
then 1
ELSEIF DATETRUNC('month', [Order Date]) >= DATEADD('month', -24,(DATETRUNC('month',TODAY()) ) )
and DATETRUNC('month', [Order Date]) <= DATEADD('month', -13,(DATETRUNC('month',TODAY()) ) )
then 2

END

I kind of compacted it all into one calc.  Yours does a nice job of compartmentalizing the logic into concise calcs.

Bottom line, the key is making that dimensional R12 value to run the chart along.

1 of 1 people found this helpful
• ###### 10. Re: Comparing Multiple R12 Based on Single Date (Today)

Hey guys!

Thank you Joe Oppelt! I started on the same lines as you have and then decided to move repeated chunks of code into separate fields. I feel a lot better that way.

Matt DiPippa, the calculation might be daunting at the first glance but it is quite simple. Let me break it down to you.

1. Let's anchor our period 1 start and end dates in separate fields. That is what the below two fields are doing. The calc used in period end takes in tomorrow's date (TODAY() + 1) and then calculates the start of the month and subtracts a day. So, if we are in the middle of the month, then we get the previous month's last date. If we are on the last day of the month, then we calculate the month start of tomorrow which will be tomorrow and then subtract a day which will be today.

2. Now coming to the actual calculated field (R12 Periods), we are using the above fields and using them to tag the order dates. If the date falls in between the above dates, then it's period1. When DATEADD function adds -1 year to a date say 2020-02-14, it would return 2019-02-14. Thus, we subsequently calculate each period by adding the appropriate negative number to take the dates backwards. Hope I made myself clear and you understood.

//Rohit

• ###### 11. Re: Comparing Multiple R12 Based on Single Date (Today)

Thank you both for the assists here!