Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Need a little help on a calculation.


iDEViate
 Share

Recommended Posts

Hey guys,

 

I usually just troll around on here but am having some trouble with a calculation thats needed to complete an attendance tracking database i'm building for the place I work for. First off I've only been using FileMaker for about 4 months now so I'm by no means a developer and I truly hope one of you all can help me with this.

 

Heres the way it works. Each day missed is 1 point. For each 30 days of perfect attendance the employee gets -1 point. After 90 days the employee will get -4 points. And after 180 days every point expires.

 

I really hope this is easier to do than I've been thinking it is. But I've just been spinning my wheels trying to get it to work.

 

Thanks ahead of time for the help.

Link to comment
Share on other sites

Well boys I think I got it..

 

If ( Sum ( Points::Sorted_Point_Value ) > 0 ; Case ( Get ( CurrentDate ) > Max ( Points::Date ) + 150 ; Sum ( Points::Sorted_Point_Value ) - 6 ; Get ( CurrentDate ) > Max ( Points::Date ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 5 ; Get ( CurrentDate ) > Max ( Points::Date ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points::Date ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points::Date ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )

Link to comment
Share on other sites

If you're using that as a field definition, be sure to set the calculation field to "unstored" or it won't work due to not noticing when Get(CurrentDate) changes from one day to the next.

Link to comment
Share on other sites

Yeah its unstored.. Heres what I ended up with but it breaks.

 

If ( Sum ( Points::Sorted_Point_Value )  ≥  6 ; Case ( Get ( CurrentDate ) > Max ( Points: :ate ) + 150 ; Sum ( Points::Sorted_Point_Value ) - 6 ; Get ( CurrentDate ) > Max ( Points: :ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value  ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  5.75 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 150 ; Sum ( Points::Sorted_Point_Value ) - 5.75 ; Get ( CurrentDate ) > Max ( Points:ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  5.5 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 150 ; Sum ( Points::Sorted_Point_Value ) - 5.5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  5.25 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 150 ; Sum ( Points::Sorted_Point_Value ) - 5.25 ; Get ( CurrentDate ) > Max ( Points:ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  5 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  4.75 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 4.75 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  4.5 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 4.5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  4.25 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 120 ; Sum ( Points::Sorted_Point_Value ) - 4.25 ; Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  4 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 4 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  3.75 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 3.75 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  3.5 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 3.5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  3.25 ; Case ( Get ( CurrentDate ) > Max ( Points:]ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 3.25 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  3 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 3 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  2.75 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 2.75 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  2.5 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 2.5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  2.25 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 90 ; Sum ( Points::Sorted_Point_Value ) - 2.25 ; Get ( CurrentDate ) > Max ( Points:\ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  2 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 2 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  1.75 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 1.75 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  1.5 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 1.5 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  1.25 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 60 ; Sum ( Points::Sorted_Point_Value ) - 1.25 ; Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  1 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - 1 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  .75 ; Case (  Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - .75 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  .5 ; Case ( Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - .5 ; Sum ( Points::Sorted_Point_Value ) ) )


&


If ( Sum ( Points::Sorted_Point_Value )  =  .25 ; Case  ( Get ( CurrentDate ) > Max ( Points:ate ) + 30 ; Sum ( Points::Sorted_Point_Value ) - .25 ; Sum ( Points::Sorted_Point_Value ) ) )

 

The issue i have now is. When someone has a point on say 1/20 then another on 2/21 then another on 3/23 they only get -1 credit when they should get -2 credit. So i've got part of it but i need to keep going with it. Any thoughts guys?

Link to comment
Share on other sites

Can you clarify, without me having to parse all of your code and all of your informal commentary on it?

 

Your formula is difficult to read and unnecessarily convoluted — not the cause of your problem but makes it difficult to read. You should use Let () to summarize all the chunks of code that keep getting repeated over and over in your existing code.

Link to comment
Share on other sites

oh Awesome, thanks for that bit of insight. I'll clean my code up and repost it.

 

Heres the core of my code cleaned up.

 

Let ( [ PV = Sum ( Points::Sorted_Point_Value ) ; MX = Max ( Points::Date ) ; CD = Get ( CurrentDate ) ] ; If PV  ≥  6 ; Case ( CD > MX + 150 ; PV - 6 ; CD > MX + 120 ; PV - 5 ; CD > MX + 90 ; PV - 4 ; CD > MX + 60 ; PV - 2 ; CD > MX + 30 ; PV - 1 ; PV ) ) )

 

This rolls the points off fine from the last point. The issue i'm having is say

 

Occurrence #1 - 1 point on 12/20/2011

Occurrence #2 - 1 point on 2/02/2012

 

The employee will then have 1 point with this calculation. Even though before the 2nd point was created the calculation had rolled that point off. I need to devise a way to store the previous amount that had been rolled off.

 

Hope that makes sense.

Link to comment
Share on other sites

Yikes! that Let() statement didn't help. Too condensed!

 

What's not clear to me is how you're recording attendance in order to know when the absences are.

Link to comment
Share on other sites

  • 2 weeks later...

You seem to be mixing "points" with attendance. They really need to be separate. Points are earned or lost as a result of attendance. Your "points" table seems to be absences, yes?

 

I've never heard of "points" for attendance like this, so you have to explain what the rules are. Are points reset at the new fiscal year, or do people keep going into the hole for perfect attendance. Is 160+ the last increment? If someone has perfect attendance for 400 days, is that still -6?

 

Do the negatives accumulate? example, if someone has perfect attendance for 95 days, do they get -4, or do they get the -1 for the first 30, plus the -2 for the first 60, plus the -4 for the first 90 (total of -7)?

 

What does it mean to have positive or negative points? You say "After 30 days of perfect attendance...", it that calendar days or work days? What about when someone goes home sick after a few hours of work, is that an absence?

 

I ask these questions because it's the exceptions that make the script or calculation difficult. 95% of the time, things fall into the typical case, but it's what to do about the outliers that you have to think about.

Link to comment
Share on other sites

You seem to be mixing "points" with attendance. They really need to be separate. Points are earned or lost as a result of attendance. Your "points" table seems to be absences, yes?

 

I've never heard of "points" for attendance like this, so you have to explain what the rules are. Are points reset at the new fiscal year, or do people keep going into the hole for perfect attendance. Is 160+ the last increment? If someone has perfect attendance for 400 days, is that still -6?

 

Do the negatives accumulate? example, if someone has perfect attendance for 95 days, do they get -4, or do they get the -1 for the first 30, plus the -2 for the first 60, plus the -4 for the first 90 (total of -7)?

 

What does it mean to have positive or negative points? You say "After 30 days of perfect attendance...", it that calendar days or work days? What about when someone goes home sick after a few hours of work, is that an absence?

 

I ask these questions because it's the exceptions that make the script or calculation difficult. 95% of the time, things fall into the typical case, but it's what to do about the outliers that you have to think about.

 

 

Anytime someone misses they are going to get a point so its one and the same thing. Negatives do not add up.

 

I have this working but I really feel it could be done simpler. And my solution breaks if you aren't able to capture the value previously rolled off.

 

Heres an example of a advisors attendance.

12/18 1 point

12/19 1 point

12/20 1 point.

12/25 1 point. Roll off date for this one point will be + 30 days if no other points have been accumulated. + 60 days will roll off 2 points. and 90 will roll off 4.. 120 will be 5 and 150 will be 6. 7 Is termination and points expire after 180 days.

 

What i'm doing now is using the calc outlined earlier but modified a tad to include a field that holds the previously rolled off value.

 

A server side script changes the points to 0 after 180 days. Runs once a day.

 

I know this is really weird. Its for a call center which i work at. I just took this on as a project to learn FileMaker.

 

Once again i have this working i just know there has to be a better way of doing it. As i've learned with my experience with FM I usually end up way over thinking things.

Link to comment
Share on other sites

I would approach this by having each portal row within the last 180 days calculate the number of days since the previous absence. A recursive CF could probably do the same thing. Any period 30 days or longer gets a negative 1 for each 30 days.

Link to comment
Share on other sites

I would approach this by having each portal row within the last 180 days calculate the number of days since the previous absence. A recursive CF could probably do the same thing. Any period 30 days or longer gets a negative 1 for each 30 days.

 

I think your on the right track with that. The way i'm doing it really isn't ideal.

 

The boat i was missing was how to get the value from the previous portal row. What function would I be using for that?

 

Sorry i'm still rather new to FileMaker.

Link to comment
Share on other sites

http://www.msprotege.com/members/kyspeed/samplefile.fp7

 

The link above is a sample file showing what you described but my issue is.. The calculation isn't correct across all records? One is perfectly fine and correct.

 

What am I over looking?

 

 

***EDIT

 

Looks like if i use RecordNumber instead of RecordID it works just file. But can we confirm if the above sample file exhibits expected behavior ?

Link to comment
Share on other sites

Here's how I'd do it. I left some intermediate calcs in there so you could see what's going on.

 

Note the additional table occurrence on the graph and the relationship (including the sort option.)

samplefile.fp7

Link to comment
Share on other sites

Shame i'm not a Gold member.

 

Considering joining. really it would be worth it so i could get this calc finally completely worked out.

Link to comment
Share on other sites

The file you posted breaks many ways.

 

Overview of the four records in the advisors table

 

Record 1. John Smith

He should have 5 points. 1/16 should have been removed.

 

Record 2.James

should have 1 point not 3

The 104 day gap should've caused - 4 points to be removed which would've taken him down to 0 then the point on 5/1 bringing him to 1.

 

 

Record 3. Susan has -1.. She should have 0

 

 

Record 4. Will is correct

 

 

 

Thanks for your help. As the system i'm using now is copying the "current rolled off point value" into a field and subtracting that from the long case statements i listed earlier. It works but i really don't like to use it because if data isn't entered at the time of the absence it breaks. IE i put data in thats 3 months old and the system credits them with 3 months of perfect attendance when in reality they may not have had perfect attendance for 3 months the data just may have not been entered yet.

 

 

 

 

EDIT..

 

Adding the evolution of the sample file. I made some changes

 

It does calc correctly now on the current record and hopefully won't break once i write all the other scenarios into the calculation. Still think it could be simpler

SampleFile Old.fp7

Link to comment
Share on other sites

Well it works now and doesn't break. The filtered relationship and unstored calc's are really taxing on the solution. Running really slow now when switching records but I guess my users will just have to deal with that.

Link to comment
Share on other sites

That's going to be a drawback of calculating the date differences on the fly. You can work around this by taking storing the calculated Prev Absence in a regular Date field. This could be done with a script run once a day, like with a server-side script.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use