I am new to Tableau and this in my first post - so apologies if this is posted in the wrong forum.
No problem, just moved it for you.
With regard to your question - This is quite possible in Tableau, its just how you want to do it.
You want a user to select a date, and then for the period to reflect the selected date + 27days?
or something else?
Your going to need either
DATEDIFF('day',[1st Date],[2nd Date])
and maybe IF function.
If you could provide a little more detail, and ideally an example I can help you better.
Thanks for your reply, I have a shipment departure date I am trying to build a dashboard for my client, but they do not operate using calendar months – instead they want to see data based on 27 day periods. The start date I want to use is 06.Jan.15 and I want to create a period for each 27 day segment.
I think somehow I need to group all dates which fall into each period, I did try using an IF formula but this doesn’t seem very practical as I would need to constantly add new date ranges as the months/years go by.
So in summary, if I have a shipment which departing on 10.Jan.16 I want related data to display under “2016 Period 1”, if I have a shipment departing on 02.Feb.16 I want this data to display under “2016 Period 2”.
Hope this makes sense.
116012012343807691.png 12.2 KB
Not very sofisticated but could work.....
if [Date]>=#5-1-2016# and [Date]<=#5-1-2016#+27 then "Period 1"
elseif [Date]>#5-1-2016#+27 and [Date]<=#5-1-2016#+(27*2) then "Period 2"
elseif [Date]>#5-1-2016#+(27*2) and [Date]<=#5-1-2016#+(27*3) then "Period 3"
elseif [Date]>#5-1-2016#+(27*3) and [Date]<=#5-1-2016#+(27*4) then "Period 4"
elseif [Date]>#5-1-2016#+(27*4) and [Date]<=#5-1-2016#+(27*5) then "Period 5"
elseif [Date]>#5-1-2016#+(27*5) and [Date]<=#5-1-2016#+(27*6) then "Period 6"
elseif [Date]>#5-1-2016#+(27*6) and [Date]<=#5-1-2016#+(27*7) then "Period 7"
elseif [Date]>#5-1-2016#+(27*7) and [Date]<=#5-1-2016#+(27*8) then "Period 8"
Good work Norbert, I cant (yet) think of anything much better...
The problem is 'holding the +27 data value and reusing it?!
Norbert's method circumvents that problem by constantly using the same starting point.
Let's wait.....you never know if there is a briljant kid on the block coming up with a "nifty" solution;).....but up till now I like working with a "fixed" reference point.
There maybe a better way, I never say never on this forum (learnt my lesson), but I can't think of it....
As mentioned the crux of the issue with automating is holding the +27 days date and then reusing it, for the next calc and then using that new date and so on... < Norbert cleverly circumvented this by keeping a single fixed date only.
In Excel we could use $ to fix the cell but that isn't available in Tableau.
Someone may come along with a super clever way... you never know!
I've got one...working on it...
1 of 1 people found this helpful
You could use a calc field like this for a [Period #] :
floor( datediff('day', [Start Date Parameter], [Ship Date]) / 28 ) + 1
where [Start Date Parameter] could be a Parameter
with a default value of January 5, 2016 in your case.
Hope this could help.
I think you mean that the periods are 28 days, not 27, since all the ones you list are 28 days, is that correct?
The way I approached this problem is that it's one about binning the dates into 28 day periods and making sure that the period/bin numbers are accurate.
Here's a calc:
IF [Date] >= #2016-01-05# THEN
INT((DATEDIFF('day',#2016-01-05#,[Date])) / 28) + 1
INT(DATEDIFF('day',#2016-01-04#,[Date]) / 28) + -1
It uses the notion of an epoch date set to 2016-01-05, then bins each 28 day period. In order to get the periods prior to the 1st period and set those as negative numbers there's a 2nd calc in the ELSE part. Finally there's no 0th period, so the bin calcs use +1 and -1.
I used SIZE() in the workbook with a compute using on the Date to validate that every period is 28 days (other than the first and last in the sample data that I created). Here's the view:
v9.0 workbook is attached.
binning 4 week periods.twbx 29.3 KB
ha-ha, it always happens to me Norbert!!
I learned some time back - never say never in this forum, its like setting a challenge!
But is not so bad... the OP gets what they need and we learn something new
...consistently amazed not only by the variety of techniques, tricks and hacks but by members willingness to help and share (a BIG thank you Shawn, Yuriy & Jonathan)