7 Replies Latest reply on Mar 25, 2014 11:28 AM by Noah Salvaterra

# Relative Date: Last 24 hours data- Start : 7:00 am, yesterday - End: 7:00 am, today

I am at a site and need to full fill the following request from the customer: For each day,

Customer wants last 24 hours analysis such that start time = 3/24/2014 7:00 am and end time = 3/25/2014 7:00 am.

thanks

• ###### 1. Re: Relative Date: Last 24 hours data- Start : 7:00 am, yesterday - End: 7:00 am, today

Can do with a few calculations.

Today at 7am:

datetime(str(today()) + ' 7:00 am')

Yesterday at 7am:

In Window:

[MyDate]>=[Yesterday at 7am] and [MyDate]<=[Today at 7am]

Filter on this last calculation being true. Normally I try to demonstrate this with a workbook, but I don't have anything handy with current data so it wouldn't be a very interesting view. Let me know if you have any trouble.

N.

• ###### 2. Re: Re: Relative Date: Last 24 hours data- Start : 7:00 am, yesterday - End: 7:00 am, today

Hi Noah,

Thanks for you reply.  Attached is the representative data source.

Actually, the customer wants to see analysis (the number of alarms instances) for last 24 hours (from 7:00am to 7:00 am) each day.

Each day when customer opens the analysis he should have the analysis for last 1 day (i.e. from 7:00 am yesterday till 7:00 am today).

In your answer, the result set will be 12:00:00 am (yesterday) till  11:59:59 pm.

The analysis looks like:

Number of alarms instances
3/24/2014 12:00:00 am       3/24/2014 11:59:59 pm         64

It should be:

Number of alarms instances
3/24/2014 7:00:00 am       3/25/2014 7:00:00 am            62

• ###### 3. Re: Re: Re: Relative Date: Last 24 hours data- Start : 7:00 am, yesterday - End: 7:00 am, today

I tried Noah's solution and it seems to work for me, i tested it with a sample xls db:

I got the same numbers in Tableau as in this excel example, so i figure it works.

• ###### 4. Re: Re: Relative Date: Last 24 hours data- Start : 7:00 am, yesterday - End: 7:00 am, today

I was just about to hit send on a more complicated solution with an if statement when I realized an easier way to do. Just subtract 7 hours from your date time to create an adjusted date. Note this is wrapped in date to truncate the time portion of this adjusted date to avoid confusion.

If you'd prefer, you could also go the other direction as well to make this a reporting date:

• ###### 5. Re: Re: Relative Date: Last 24 hours data- Start : 7:00 am, yesterday - End: 7:00 am, today

If you just want the most recent day, my original solution should work, or the simpler one which can be filtered based on a comparison to today(), or dateadd('day',-1, today()) or a more complex criteria if you want multiple days to be reported after a weekend or holiday for example.

If this is just an issue of how the date is displayed, you could always add some formatting on the adjusted date after it is created:

Hope this helps. If not could you indicate what field(s) in the data you provided indicates an alarm, how many alarms you expect for each 7-7 period, and how you would like this output to look in a way that corresponds to the sample data.

Thanks,

N.

• ###### 6. Re: Re: Re: Relative Date: Last 24 hours data- Start : 7:00 am, yesterday - End: 7:00 am, today

Hi Noah / Michel,

Thanks a lot for your valuable inputs. Attached, you will find the required output.

I have also attached the data source file and twbx.

The data is from nov-dec 2013. However, in production the last extracted data will be 7:30 am each morning.

Kindly look into this. The customer will open this sheet each day and whenever they open it or download it from Tableau server the last 24 hours data (from 7:00 am till 7:00 am) will be displayed.

User does need to do any slider action or enter anything into the filter. The same sheet/view will be downloaded as a report in pdf / png (7:00 am till 7:00 am).

If you notice in the twbx, the time is UTC. I have created a calculation field to the set to GMT+4 for reporting.

• ###### 7. Re: Re: Re: Re: Relative Date: Last 24 hours data- Start : 7:00 am, yesterday - End: 7:00 am, today

I think the solution outlined should work. I implemented on the data you provided, however, since the data you provided is several months old I used a parameter for the current date (for testing) rather than the function today() which would provide the current system date. In production you should change the filter to use today().

Does this do it? If it is failing I think I'm misunderstanding what is required.

N.