Splitting data by date

[former_member318041]

The data in the spreadsheet contain two timestamps "INM Downtime Start" and "INM Downtime End".

The task is to calculate and display the "Availability" per day.

The challenge is that the calculated "INM Downtime (in minutes)" expands over more than one day ("Service Minutes" = 1440) and needs to be split and adjusted accordingly to the appropriate day ...

The expected result should look like:

Is there experience of how to handle this issue?

Any racommendations are highly appreciated!

TIA,
Henning

[mhmohammed]

Hi Henning,

This is an awesome requirement, earlier I thought it was going to be difficult without additional details, but then, I looked at the timestamps and did some analysis, and it makes sense.

It'd be a lot easier for us if you can provide an excel sheet with the data in top table, so we don't have to manually create the data in excel.

Also, what version of BI do you have? You'll need BI 4.2 SP6 or higher version, because it has a Time Dimension that'll help you display all the missing dates, even if you there is no data for specific dates. If you don't have BI 4.2 SP6, you'll have to create an additional query to bring in all the dates in the calendar.

Thanks,
Mahboob Mohammed

[former_member318041]

Hi Mahboob,

thank you for your quick answer and the solution you have in mind!
Sorry for not providing all the necessary details in the first place - I have now attached the sample data, just in case ...

I am using the version 4.2 SP6.

Best regards,

Henning

cusersa298483desktopsample.txt