10 Replies Latest reply on Dec 8, 2016 8:25 AM by Łukasz Majewski

# Dealing with Date/Time related data

Hi all,

I have what seems to be a very simple requirement but after trying different ways I am unable to figure this one out. I'll continue looking at this but thought it wouldn't hurt to make a post in parallel. Sometime writing about it helps the thought process too...

Description of information we have to work with:

I have a data set with a list of locations and each location has a Date/Time stamp of some work that was performed.

In this particular scenario, the work performed was to improve energy efficiency and we know that for each location, on that specific date, we START to save x number of kWh each day going forward.

Challenge:

I'd like to add a single line representing the cummulative kWh's for all locations combined and plotted over time so that we can see as new sites are done the impact to the total over time....sounds simple right? THat's what I thought...I have a feeling the answer lies in the LOOKUP function but I have not used that one very much. I feel like I've done much more complicated stuff with Tableau. Am I having a case of "Tableau burnout"?

Edit:

Here is a sample of data in case it wasn't clear

Site
Date Done
A06/01/2016
B06/15/2016
C08/27/2016

Let's just say for each site, it would save 100kWh/day going forwrd from those dates. How can I plot that on a time axis? DO I need to add a parameter with the value of 100 and do some lookup formula with the date field?

Thanks!

-Derek

• ###### 1. Re: Dealing with Date/Time related data

If you are unable to hit upon the solution by yourself, then posting a sample packaged workbook will help us help you.

Cheers,

--Shawn

• ###### 2. Re: Dealing with Date/Time related data

The easiest way is manage this logic in formula.

<<Solution One>>

[Goal Stretched]

if ([Site]="A" and [Date]>=date(#2016-06-01#))

or ([Site]="B" and [Date]>=date(#2016-06-15#))

or ([Site]="C" and [Date]>=date(#2016-08-27#))

then [Goal]-100

else [Goal]

end

If you have many sites and want to handle in different table, here is a

<<Solution two>>

Table 2

Make relationships only on [Site], not on [Date]

All the data should be aggretaged in this case.

[Goal 2]

if attr([Date])>= [Date Tbl 2] then sum([Goal]) - SUM([Table2].[Value]) else sum([Goal]) end

Thanks,

Shin

• ###### 3. Re: Dealing with Date/Time related data

The data I am working with looks exactly like what is shown in the table.

I want to plot the cumulative savings over a time axis for all sites combined knowing that each site saves 100kWh/day starting on the start date in the table.

So how it should look in the above example is that we would see a line start at the 100kWh level on 06/01/2016...it would go up linearly (100kWh/day) until it hit 06/15/2016, then start going up faster by 200kWh/day...until it hit 08/27/2016 then go up 300kWh/day onwards...and so on for any other sites done during the year, until the end of the year.

It would look like this:

This would effectively give us a graphical representation of the cumulative savings for all the sites done in the year and see the impact as more are added.

It sounds like such a simple thing but I can't figure out how to plot this on a time axis when all I have is the Start Dates.

• ###### 4. Re: Dealing with Date/Time related data

... of try making your [Start Date] field continuous (green).

Cheers,

--Shawn

• ###### 5. Re: Dealing with Date/Time related data

Been trying to do that. Like I said, it sounds simple...but it's not.

I have attached sample workbook. I also added some annotations so you can understand what I should be seeing. Please have a look and let me know.

Your time is very much appreciated!

-Derek

• ###### 6. Re: Dealing with Date/Time related data

Do you mean this type of chart?

Thanks,

Shin

• ###### 7. Re: Dealing with Date/Time related data

No. I think you are not understanding the ask. I'm not so sure how else to explain it. It's a simple thing I am after...

Just think of Site "A" in the example...

That site should start out at zero (0) @ 06/01/2016 and increment by 100 each day beyond that day. (i.e. 5 days later I should see a savings of 500kWh in the line chart on 06/06/2016).

When other sites are added that would just accelerate the speed at which the lines goes up. As in my example, once 2 sites have been done, then from that point the line should rise by 200 each day.

I've included a simple table, with a simple workbook example and even a picture of how it should look. I don't know how else to explain.

Maybe one of the greats Joe Mako could look at this. It should be a walk in the park. It's not very complicated. I suspect we need to use INDEX() or LOOKUP() in some way but have been unable to figure it out yet.

-Derek

• ###### 8. Re: Dealing with Date/Time related data

I think I understand the issue now.

I can provide my approach, but need to prepare these data set.

This is the table to show date for all sites.

Edit data source and pivot data

create relationships

[Saving 2]

previous_value(0)+running_SUM(zn(sum([Sheet1 (Book1)].[kWh Saved/Day])))

Data

Thanks,

Shin

• ###### 9. Re: Dealing with Date/Time related data

Could achieve easier

Thanks,

Shin

[Cum Saving]

previous_value(0)+running_sum(sum([kWh Saved/Day]))

Thanks,

Shin

• ###### 10. Re: Dealing with Date/Time related data

Hi,

Here is a solution up to today():

Łukasz

2 of 2 people found this helpful