7 Replies Latest reply on Sep 3, 2018 10:56 PM by Shinichiro Murakami

# Splitting Up an Event Duration to Different Months

Dear Community,

I have the following data showing the duration (days) of events:

This data can e.g. show the days a bus is out of service due to maintenance.

I want show the out of service days per month. What I can do with tableau quite easily is showing the duration based on the start date:

This chart is misleading, as a bus was out of service in march. So how can I create a visualisation, that splits that duration and assignes it to the respective months. So ID 1 will count with 3 days for February and 13 days for March, resulting in a view like this:

Packaged workbook attached with dummy data, version 10.4.1

• ###### 1. Re: Splitting Up an Event Duration to Different Months

HI Marco,

You can try below approach:

Pivot the data across start date and end date.

Your data will look like this:

Create below fields:

1.  To get the last day of start month:

Start month last date = if [Name]="Start" then datetrunc('month',dateadd('month',1,[Date]))-1 END

2.  Ideal duration in start month:

Start Ideal Duration = DATEDIFF('day',[Date],[Start month last date])

3. Ideal duration in end month:

end Ideal Duration = { FIXED [ID]:max([Duration]-[Start Ideal Duration])}

4. Ideal Duration =

if [Name]="Start" then

[Start Ideal Duration]

ELSE [end Ideal Duration]

END

• ###### 2. Re: Splitting Up an Event Duration to Different Months

Dear Meenu,

thanks a lot for your response. That is quite a good idea. However, this works only if the duration occurs within two months. For example if the start is in February and the end in April, I can get the days for those two months, but the March is not covered. Therefore I was hoping for another approach.

Best Regards

• ###### 3. Re: Splitting Up an Event Duration to Different Months

HI Marco,

It was do-able (so-said) but in other words, almost impossible without understanding whole the table calc logic..

It's extremely tough.

This is still very small sample and I cannot guarantee that all the calc is correct with full data.

Fist of all, need to prepare another calendar table to cover whole range.

JoIn it

Very complicated step by step calc.

Thanks,

Shin

• ###### 4. Re: Splitting Up an Event Duration to Different Months

Thank you very much Shin. Complicated, but works.

I just found a similiar request:

This solutions worked as well for me.

• ###### 5. Re: Splitting Up an Event Duration to Different Months

I know, but that's only available from version 10.5

Shin

• ###### 6. Re: Splitting Up an Event Duration to Different Months

Hi Shin,

It worked for me on 10.4.

• ###### 7. Re: Splitting Up an Event Duration to Different Months

If you are talking about 1 on 1, yes available on 10.4.

But increases the number of data significantly if you have more.

10.5 only is "Range Join"

Thanks,

Shin