FIFO Method in Spotfire

[Winda Tobias]

I am trying to figure out a way to calculate the FIFO method in Spotfire to calculate the average length of stay.

 

as an example, if I have 400 attendees to a festival in a day, and their entry and exit count as below:

 

 

 

 

 

 

 

 

 

 

 

 

Hour Entry Exit

1 PM 150 0

2 PM 100 50

3 PM 100 100

4 PM 50 150

5 PM 0 100

 

If I want to know how long people are staying for, based on manual calculation, it will be:

50 People stays from 1-2 PM (1HR)

100 People stays from 1-3 PM (2HR)

100 People stays from 2-4 PM (2HR)

50 People stays from 3-4 PM (1HR)

50 People stay from 3-5 PM (2HR)

50 People stay from 4-5 PM (1HR)

 

so as a conclusion, there are 150 people who stayfor 1 hr and 250 people stay for 2hr.

Are there any formula I can use in Spotfire for this if I only have the hour and entry/exit counts Thank you!

[Fabian Duerr]

Can you help me to understand your numbers I need some clarification here before I continue

 

For example why are there just 50 people from 1-2 pm 150 enter and 0 exit... Please add your calculation to your original post. Thanks

 

There is something I miss :(

 

[Winda Tobias]

Hi Fabd, thank you for responding.

The reason why I marked the 50 people leaving for 1 hr was that when the first 150 entering at Hour 1 and when the 50 exits at hour 2, shouldn't they be calculated asstaying for only 1 hr

I believe the calculation above was Run.Entry as the cumulative Entry and Run. Exit as cumulative exit and so the AtEndOfHour is the total number of people who had stayed

[Fabian Duerr]

Yes, of course you are right. That's actually pretty clear. I don't see why I didn't see that two days ago... :D. Let me think about this problem.

[Fabian Duerr]

So far I couldn't figure out how to do this with a calculated column but I could provide a data function solution if this is helpful for you.

 

The idea would be to read in your entry and your exit columns and build two vectors. Then I could subtract both vectors and count the resulting numbers in the result vector.

 

Let's say your have 6 people entering at time0, 7 entering at time1, ... and 1 leaving at time0, 4 leaving at time1,... 

 

Just follow this 'graphical' solution to get the idea.

 

 

Here time0, time1, .. define the timeframe from your original table. So this would be row1, row2,...

 

And time spent will be in units of this time frame. Which is one hour in your case:

 

0: left in the same hour; 1: spent 1-2 hours; 2: spent 2-3 hours