Spotfire document property sorcery

[Ash Mishra]

The embedded data on dxp file consists of 10 employees attendance record. I have a bar chart that shows % of attendance by On Time (when Actual = Schedule), Within 5 Hours, and Within X Hours (where X is controlled by integer document property where value on document property is changed using slider).

There are 10 employees, 6 of them are on time, and 2 of them are within 2 hours, and the remaining 2 are within 5 hours.

When the slider is set to 0, as expected, you see 60% On Time, and 40% Within 5 Hours. When we set slider to 2, then as expected On Time is still 60% while Within 5 Hours is now 20% and Within X Hours at 20%.

All of this is working as intended. Now my real challenge is that I want to switch this around so I am entering in % to figure out the hours. I would like to find outgrace hours for employees to come in to work and still count as On Time. If I enter in80 as my "X %", it should then return 2 hours.

[Fabian Duerr]

You are looking for the X percentile in time difference...

Firste make a new column with the time difference. Then Create a new doc propertie to enter your value like 80,60 ... that you can use to find the 80,60... percentile.

I created a calculated column in the attached file called "Limit". Of course you could just output this as another property as well.

[Ash Mishra]

Edit:

 

Fab, you are on to something. Appreciate the DXP as well. Let me add more clarity.

 

Enter 100 % and it will return 0 (Time difference of 0 between Actual and Schedule), 90 % On Time wtihin 0 Hour, 80% On Time within 2 Hour, 70% On Time Within 2 Hour, 60% On Time within 5 Hour.

 

Basically trying to figure out how soon employees need to come to work in order to make to defined %.

[Fabian Duerr]

But this is what the my function gives. For 100% it's 5 hours, for 80% it's 2.6 hours...

 

Add more data and you will see that it works well.  Calculating percentiles for very little data points is not so good. It's more a step function than being continuous.

[Ash Mishra]

Let me try and get back to you. Perhaps I am not assessing this correctly. Thank you.

[Ash Mishra]

Fab,

 

If I select within 2 Hours, I return 20% within 2 hours, 20% within 5 hours, and 60% On Time. This is based on existing logic and makes sense since 6 out of 10 employees were on time, and 2 were within 2 hours, and the rest were within 5 hours.

 

Now when I enter 80% on time, I was expecting it to return 2. (Because 80% of the employees were within 2 hours). I may be doing this wrong, but I took the limit and set as my Y axis, and took the average of the limit and set as my X axis. See attached screen.

 

[Fabian Duerr]

I double checked and I think everything works fine. The details are in the percentile function. It just needs more data points (otherwise it will interpolate) and also it's maybe an issue that your data points are completely identical (0,0,0,0,0,0,2,2,5,5). But feel free to add some small random number to Diff(h) to fix this:

 

+ RandBetween(-100,100,147) / 10000      

 

To get a better understanding of percentiles create two data sets that go from 1-10 and from 1-100 and check the normal probability plot. You can also use this plot later on your data set (as it gets bigger) to figure out the number you are looking for.

 

Percentiles are calculated based on rank functions. So as an alternative you can work with a customized rank function like this one:

 

(1-((Rank([Diff(h)],"ties.method=first")-1)/Count([Diff(h)])))*100

 

Adjust it to your needs.

 

 

[Ash Mishra]

Greatly appreciate you taking time to explain and work throughthe sample file. I will go ahead and marked as solved. Thanks Fab!

[Fabian Duerr]

You are welcome. Thank you for confirming.