1 2 Previous Next 24 Replies Latest reply on Jan 13, 2014 10:05 AM by bruns

    Count Calculation Help

    bruns

      Title

      Count Calculation Help

      Post

           I have an employment history portal that records past employment and I have a Tenure field that show how many years, months and days an employee has been employed with us in the past based off hire date and term date.  Is there a way to put a field at the bottom of the portal that counts these in the same format.  I am enclosing screen shot of the portal and the current calc.  If anyone can help, I appreciate it. 

      Capture1.PNG

        • 1. Re: Count Calculation Help
          bruns

               2nd screen shot

          • 2. Re: Count Calculation Help
            bruns

                 Last Screen shot

            • 3. Re: Count Calculation Help
              bruns

                   Calc

                    

                    

                    

              • 4. Re: Count Calculation Help
                philmodjunk

                     Do you want a count or a total?

                     If you want a total, I suggest modifying the original calculation so that you have one field for the years, one for the months and one for the days. That would Make it easier to compute total years, months and days before doing a conversion to adjust the resulting totals so that you don't see a result that has something like 2 years, 20 months...

                • 5. Re: Count Calculation Help
                  bruns

                       Is it possible to do in current format to keep in 1 field?

                       I guess I need a total to add up all.

                        

                       Thanks

                  • 6. Re: Count Calculation Help
                    philmodjunk

                         It's simpler not to. You can take the three individual values and use a calculation field to combine them to get the text and number combination your current calculation provides. A combination of layout text and merge fields also may be used.

                         cTenureYear & " years, " & cTenureMonths & " months, and " & cTenureDays & " days"

                         or as merge fields in your portal row:

                         <<Table::cTenureYear>> years, <<Table::cTenureMonths>> months, and <<Table::cTenureDays>> days"

                    • 7. Re: Count Calculation Help
                      bruns

                           I see the merges part and work with that one, but how would I change my calc of date term  - date hire to just give me years, and then months and then days in separate fields?  Thanks

                      • 8. Re: Count Calculation Help
                        philmodjunk

                             Make copies of your calculation field and edit each to limit the last line to just y,  m and d for each of the three fields. You can also remove the corresponding parts where the removed variables are assigned a value.

                        • 9. Re: Count Calculation Help
                          bruns

                               I am sorry Phil, I am not understanding your last post.

                          • 10. Re: Count Calculation Help
                            philmodjunk

                                 Go to Manage | Database | Fields. Select the field by clicking it. Click duplicate. Rename the field and click change. Double click the new field or click Options to open the calculation dialog so that you can edit it.

                                 To get a calculation for just the years, change:

                                 Y & " years, " & M & " months, and " & D & " days"

                                 to:

                                 Y

                                 You can also delete the two parts that start with M = and D =.

                                 Repeat for months and days but keep a different part of the expression

                            • 11. Re: Count Calculation Help
                              bruns

                                   I am getting 0 in the years and months fields.  Did I get a calc wrong?

                              • 12. Re: Count Calculation Help
                                bruns
                                /files/4e14638de3/Capture2.PNG 992x862
                                • 13. Re: Count Calculation Help
                                  philmodjunk

                                       I see no reason why you are getting a zero. I went back and found your previous thread so that I could copy the original expression in order to paste it into the Data Viewer for testing.

                                       Let ( [date1 = GetAsDate ( "5/1/99" ) ;
                                                date2 = GetAsDate ( "1/1/2014" ) ;
                                           neg = Case ( date1 > date2 ; -1 ; 1 ) ;
                                           d1 = Case ( neg < 0 ; date2 ; date1 ) ;
                                           d2 = Case ( neg < 0 ; date1 ; date2 ) ;

                                           d = mod ( Day ( d2 ) - Day ( d1 ) ; Day (
                                             Date ( Month ( d1 ) + 1 ; 0; year ( d1 ) ) ) ) ;
                                       
                                           m = mod ( Month ( d2 ) - Month ( d1 ) -
                                             ( Day ( d2 ) < Day ( d1 ) ) ; 12 ) ;


                                           y = Year ( d2 ) - Year ( d1 ) - ( ( Month ( d2 ) -
                                             ( Day ( d2 ) < Day ( d1 ) ) ) < Month ( d1 ) )
                                         ];
                                          
                                           y  & " years, " & m & " months, and " & d & " days" & ¶
                                       )

                                       Produces a result of:

                                       14 years, 8 months, and 0 days

                                       If I then delete the blue parts, I get: 14

                                       Making similar changes to get just the month result produced: 8

                                  • 14. Re: Count Calculation Help
                                    bruns

                                         My bad, I was using date that were close together therefore the year and month was "0".  That is working in the portal, how would I do the total outside the portal to show me total of years, months and days.  I can't do a regular summary total can I since date fields?  Thanks

                                    1 2 Previous Next