Sum Operational Car Over Time

[Anonymous]

I am struggling to find the solution for this problem.

I have table showing when a care was serviced, and their repair period. From this table I would like to have a KPI, on how many cars are operational and ready for duty.

To illustrate, here's the raw table:

Car Service Table

And here's the table that I am looking for

Car details, over time

And possibly creating the timeline-chart of operational cars

Simple chart showing operational car

A solution either through the script or set analysis to make this happen would be much appreciated.

[Anil_Babu_Samineni]

Try to share sample raw data and expected result? and how the solution needs to be calculated?

[Anonymous]

I am attaching the spreadsheet file..

The data is there, as well as the expected result (timeline table of events, as well as how many cars available for duty, over time)

It is preferable to be done in script level, to optimize the speed.

[Anil_Babu_Samineni]

Still not sure - How these values are calculated? Can we talk one by one - First we can talk, How 14-Feb-2018 values come for Toyita and Hando etc.?

[Anonymous]

From the raw table, if it is sorted, we know that Hando is available since 10-Feb-18. Then Toyita is available on 14-Feb-18.
As Hando has been available since 10-Feb-18, on 14-Feb-18, we then have Toyita and Hando available (marked as "1" on RESULT table). In that day, total of cars available are 2 ea (column sum)
I hope it explain.