1 2 Previous Next 16 Replies Latest reply on Apr 6, 2017 10:26 AM by philmodjunk

    Limit number in Calculation

    icotta

      I have  reports that need to be done every 3 months.  Instead of dates I need to display just the number for the month - 1 through 12.  I need to know how to make the calculation to be 1 instead of 13, 2 instead of 14 etc.  For example  if the first report is in 4, second report is in 7, third report is in 10, I need the fourth to be 1 instead of 13.  Is this possible?

        • 1. Re: Limit number in Calculation
          philmodjunk

          You seem to be asking for two different things:

          Instead of dates I need to display just the number for the month - 1 through 12. 

          and

          I need to know how to make the calculation to be 1 instead of 13, 2 instead of 14 etc. 

          Are not asking for the same thing.

           

          For the first request, you can use data formatting on a date field to only show the month number from that date. For the second, it's not clear to me why you'd ever have a value larger than 12.

          • 2. Re: Limit number in Calculation
            icotta

            I did I not find the option to only show the number for the month.  Date fields required me to enter in the format of x/xx/xx.

             

            Since I am trying to set calculations that will display the month number for month the report is due based on the previous due date it works great until I get to 4 as the original month number the next number calculates to 7, the next number calculates to 10 so the next number calculates to 13.  So what I am trying to figure our is how to make the next number go to 1 for January.  14 would need to be 2 for February, etc.

             

            Sorry for any confusion.

            • 3. Re: Limit number in Calculation
              philmodjunk

              I did I not find the option to only show the number for the month.  Date fields required me to enter in the format of x/xx/xx.

               

              Not data entry. You'd still enter a full date, but the field is set to only display the month. Enter layout. Select the field and then use the data formatting tools at the bottom of the inspector to set up a custom date format that only shows the month number.

               

              To calculate the report date requires that I know more about the record's used in your report and how they were created.

               

              If the previous record has the date of the previous report, you can use GetNthRecord to get the date and add 3 to its month. If you use the date function values for a month greater than 12 auto-matically adjust.

               

              Date ( 13;1;2017)

               

              produces a date of January 1, 2018

              • 4. Re: Limit number in Calculation
                DavidJondreau

                Maybe you're looking for the Mod() function? It's a little tricky, but this may work for you:

                 

                //x is the month of the report

                If ( Mod ( x ; 12 )  = 0 ; 12 ; Mod ( x ; 12 )  )

                • 5. Re: Limit number in Calculation
                  coherentkris

                  perhaps they are looking for the month() function?

                  • 6. Re: Limit number in Calculation
                    philmodjunk

                    If you have one record for each month's report and if the previous record is in the found set as the previous record at the time that you create the next record (note the "ifs" there...):

                     

                    Let ( [ R = Get ( RecordNumber ) ;

                               D1 = If ( R > 1 ; GetNthRecord ( YourTable::DateField ; R - 1 ) ) ] ;

                               If ( D1 ; Date ( Month ( D1 ) + 3 ; 1 ; Year ( D1 ) ) ; Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) ) )

                           ) // Let

                    • 7. Re: Limit number in Calculation
                      icotta

                      I had looked at the inspector for date format but had not seen all of the options. Real rookie mistake Correcting that everything workd fine  Thank you!

                      • 8. Re: Limit number in Calculation
                        fmpdude

                        So every three months, maybe something like this?

                         

                        IF (MOD(Month(Get(CurrentDate)) ; 3 ) = 0 ; "Yes! This month is divisible by 3" ; "Nope, this month is not divisible by 3")

                        • 9. Re: Limit number in Calculation
                          icotta

                          Thank you for your response.  I will work with that and see if it works for this situation.

                          • 10. Re: Limit number in Calculation
                            icotta

                            I want to use a number that represents the month (date is not needed for this).

                            TAR1 is set as a number field

                            TARQ1 is a calculation field would add 3 to TAR1

                            TAR2 is a calculation field that would add 3 to TARQ1

                            TARQ2 is a calculation that would add 3 to TAR2

                             

                            The first field TAR1 could be any number from 1 to 12.  That would mean that adding 3 will not always give me the correct number for the month since just adding 3 to 12 would be 15 and the number I need would be 3.

                             

                            Is there a calculation that would work to do this?

                            • 11. Re: Limit number in Calculation
                              philmodjunk

                              Yet using dates avoids all the extra fiddling with the values so maybe you DO need that date after all.

                               

                              But if you read back up through the responses, #4 shows you what you need to do if you insist on using numbers for dates without them being stored as dates.

                              • 12. Re: Limit number in Calculation
                                DavidJondreau

                                I don't think you're on the right path here with having all these extra fields. A good rule of thumb is if you have fields like field1 field2 field3, you're doing something wrong.

                                 

                                But you're asking about a calculation...

                                 

                                Let ( [

                                y = table::field ;

                                x = y + 3 ;

                                mod.x = Mod ( x ; 12 ) ;

                                result = If ( mod.x = 0 ; 12 ; mod.x )

                                ];

                                result

                                )

                                1 of 1 people found this helpful
                                • 13. Re: Limit number in Calculation
                                  icotta

                                  Thank you.  I have not used the mod() before so I was not able to make it work.  I will keep trying. I agree that a date is easier but my supervisor is very new to using a database and since once the number is entered it will not change for that participant she just wants it enter a month number (not a date) for the month for the first report and have it figure the next three.

                                  • 14. Re: Limit number in Calculation
                                    icotta

                                    Thank you.  That was exactly what I needed.  As a follow through, what would be your suggestion to avoid the additional fields?

                                    1 2 Previous Next