Jump to content
We've recently updated our Privacy Statement, available here. ×

Spotfire document property sorcery


Ash Mishra

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 %.

Link to comment
Share on other sites

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.

 

proof.jpg

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...