
1. Re: Sum of measure in time intervals
swaroop.gantela Jun 28, 2016 7:27 PM (in response to Lamia Z)Lamia,
Please see if the attached could be a starting point for you.
It required me to restructure your data as follows:
Sheet 1: DateTime, W
Sheet 2: EventNumber, EventStart, EventEnd
Sheet 3: StartInterval, EndInterval (the 2 hour intervals)
I put a Key of 1 for every row and merged all the Sheets by that key.
I then filtered to only those intervals containing and event
([Event Start]>=[Start Interval] AND [Event Start]<=[End Interval])
OR
([Event End]>=[Start Interval] AND [Event End]<=[End Interval])
and filtered only the W that matches that interval
[Time]=[End Interval]
Then the portion of W is calculated in this way
IF [Event Start]>=[Start Interval] AND [Event End]<=[End Interval]
THEN [W]
ELSEIF [Event Start]>=[Start Interval] AND [Event Start]<=[End Interval]
THEN [W]*DATEDIFF('minute',[Event Start],[End Interval])/120
ELSEIF [Event End]>=[Start Interval] AND [Event End]<=[End Interval]
THEN [W]*DATEDIFF('minute',[Start Interval],[Event End])/120
END
This link may have further information:

210043event.xlsx 11.0 KB

210043event.twbx 44.4 KB


2. Re: Sum of measure in time intervals
Lamia Z Jul 5, 2016 2:10 PM (in response to swaroop.gantela)Hi Swaroop,
thank you so much for your work. It gives me very valuable insight on the possibilities of Tableau, as I am still starting out using it.
Sorry for not getting back to you earlier.