can anyone please help me with my problem
Rajeev, in the future if you are wanting to do date calcs that involve TODAY() it would be helpful if your data included dates through TODAY(). That might be why you didn't get an answer sooner. Anyway...
In the attached workbook I shifted TODAY() back two months so it would be within range of your data. (You'll want to eliminate the shift). Then I used these two calcs to get the start and stop hours:
8 a.m. Today:
DATEADD('hour', 8, [Shift Today] )
8 a.m. Yesterday:
DATEADD('hour', -16, [Shift Today] )
Then I assumed you were wanting to filter to that 24 hour period, and created this calc for that:
Filter 8 to 8:
[Snapshot Time]>= [8 a.m. Yesterday]
AND [Snapshot Time] < [8 a.m. Today]
That should at least get you started.
EDIT: I removed the DATETRUNC() as it wasn't needed. TODAY() is already truncated to the day.
8 to 8.twbx 1.7 MB
Rajeev, there are always more than one way to solve a problem in Tableau. I'm glad you've found your own variation that suits you needs. Instead of marking your answer helpful, please mark it correct so this thread gets closed out. Cheers.
Thank you so much Shawn Wallwork sir.Appreciate all your help.This is exactly what I am looking for .Sir it would be great if you throw some light why you have taken [Shift Today]. What is the necessity of "shift today" calculation . Please guide me so that I wont make any mistakes in Future.As per my understanding , it is just Data restructuring .Please correct me if my understanding is wrong.
Oh you already mentioned in your comments "In the attached workbook I shifted TODAY() back two months so it would be within range of your data" .Sorry for my misunderstanding .
1 of 1 people found this helpful
The other option would be to shift the start of each day to be from 8am to 8am.
Something as simple as
will make anything that happened prior to 8am each day appear as if it were yesterdays activity.
Then use DATETRUNC('day',[Adjusted Snapshot Time])=DATEADD('day',-1,TODAY()) to filter for yesterday upto 8am this morning??
Also by doing this will allow you to compare days over time that run 8am to 8am (if this would be of use to you).
It was all because of you sir.You showed me the correct path.Its your efforts and Guidance because of that I am solve to solve this problem.Thank you so much sir
Could you please elaborate your statement "compare days over time that run 8am to 8am" by taking an example or by attaching a sample twbx file. I never thought this way but good to know we can achieve this . Please do define this calculation as well [Adjusted Snapshot Time]
Adjusted Snapshot Time :
I would love to attach a workbook for you but i only have tableau 9 desktop now, so if i did attach a workbook for you, you wouldn;t be able to open it.
What i mean is that once you reset the snapshot time to a day as per a definition tableau understands natively you can use a truncated adjusted date field as a column or row dimension.
Shawn Wallworkdo you know what i am trying to get at - it also seems you have almost every historic version of tableau on your machine , could you help Rajeev?
i have managed to download 8.3 and create the attached with your data.
i haven't created the calculation for a specific day, but that is pretty straight forward as described in both my and Shawn earlier posts.
rajeev adjusted days.twbx 373.2 KB
Thanks Chrish. Thanks for your valuable approach.