4 Replies Latest reply on Jul 27, 2017 4:23 AM by Mads Plesner

# Longitudinal Data analysis -  how to fixe aggregations on days but aggregate over several days

Hallo community

I'm strugling with a longitudinal data problem.

I've got at data set with many observations over time. The subjects in the data have several events (the observations) with a time stamp each. I need to calculate the total time for each subject from event to the last event. This is easy. But the trouble is that some subjects has events on several days, and the time from the last event one day to the first event the next day should not be included.

The attached workbook shows the subjects with their events aggregated on days. I need to make a calculation that aggregate totals each day, so that the result for eg subject F is 128 minutes.

Hope that you'll help me

Regards

Mads

• ###### 1. Re: Longitudinal Data analysis -  how to fixe aggregations on days but aggregate over several days

Hi,

If I understand your problem correctly, you want to take the sum of the duration per day?  If so, first get the difference between the first and last timestamp in each day:

{ FIXED DAY([Date]), [subject]: DATEDIFF("minute",MIN([Event time stamp]), MAX([Event time stamp]))}

Then take the sum of those results, per subject.

Hope this helps.

Wim

• ###### 2. Re: Longitudinal Data analysis -  how to fixe aggregations on days but aggregate over several days

Thanks Wim your solution is nearly nailing my problem.

With the "DAY(date)” function you get the number of the day in a month. But if a subject has events on marts 10 and on april 10 (subject “d” in new example) in the attached file, the date difference is a month (44.000 minutes). How do I make sure this doesn’t happen?

• ###### 3. Re: Longitudinal Data analysis -  how to fixe aggregations on days but aggregate over several days

Valid point, Mads.

Removing the DAY() from DAY(Date) should solve that.  This will force Tableau to take into account the whole date, and not just the day.  Not sure why I didn't suggest that in the first place .

Wim

• ###### 4. Re: Longitudinal Data analysis -  how to fixe aggregations on days but aggregate over several days

Thank you wim