5 Replies Latest reply: May 15, 2014 10:15 AM by Olly Bond RSS

    Calculated field ?

    LarryQ _

      I have column data with various amount fields. Their are 5 rows containing the last 5 years of data.  First column being the YEAR. Second column being the COUNT.I would like to create another column(CHANGE) with a calculated field that takes an amount from the current year in the COUNT column and divides it by the COUNT amount in the next row down which would be the previous year to show the percent change from year to year. And so on.

      Problem is I am not sure how to access this data?

       

      Example: 5587/7276, 7276/5809, 5809/5758...etc.

       

      YEAR**COUNT**CHANGE

      2013**005587**     

      2012**007276**

      2011**005809**

      2010**005758**

      2009**006590**

       

      Any suggestions would be appreciated. Thanks.

        • Calculated field ?
          Olly Bond

          Hello Larry,

           

          If you trap each year as a detail template, then you'll have to use two models, as Monarch won't let you make reference to the previous (or next) record in a calculated field.

           

          There is a trick to handle this in one model using the multi-column region, but you're limited to 40 columns in the current version of Monarch.

           

          Does you data just have five rows or are there more records?

           

          Best wishes,

           

          Olly

            • Calculated field ?
              LarryQ _

              Olly,

               

              5 rows only, with always the current year and 4 prior years.

               

               

              LarryQ

                • Calculated field ?
                  Olly Bond

                  Hello Larry,

                   

                  No need for any fancy tricks then - as long as you don't mind editing the model once a year when the report updates...

                   

                  Just trap six line detail template on YEAR, and define a Memo field called which starts on line 2 and ends on "none of the above".

                   

                  Then use calculated fields based on Textline() to give you what you want.

                   

                  Best wishes,

                   

                  Olly

                  • Calculated field ?
                    LarryQ _

                    Hello Olly,

                     

                    Thanks I will give that a try but what if I dont want to edit it once a year. Is there a way around it?

                     

                    Best,

                    Larry

                      • Calculated field ?
                        Olly Bond

                        Hello Larry,

                         

                        Yes, but I think you'll need to use a dummy multiple column region with five columns, each one character wide, starting in column 50.

                         

                        Then use lsplit(intrim(Textline(Blob;Column());5;" ";1) to deliver the Year fields.

                         

                        Comparing the Count fields would be done with:

                         

                        val(and lsplit(intrim(Textline(Blob;Column());5;" ";2))-val(and lsplit(intrim(Textline(Blob;Column()+1);5;" ";2))

                         

                        This assumes that the * characters are replaced by spaces - this can be done using Replace(Textline(Blob;Column());chr(42);" ")...

                         

                        You'll have to watch out for the last year - as there isn't a value in the empty sixth row to subtract from the final Count.

                         

                        Best wishes,

                         

                        Olly