7 Replies Latest reply: May 15, 2014 9:57 AM by Grant Perkins RSS

    Calculation of Summary Fields

    Oli _

      Hi,

      I use Monarch Pro 7

      I try to make further calculation of summary fields. If I'm in the summary window there is, so far I can see, no possibility to insert/calculte calculated fields like in the tabble screen.

      Is there a way of proceeding calcualtions in the summary window?

       

      A second questions concerns proceeding calcuations within Monarch of values coming to Monarch via External Lookup. I use a Excel file and the lookup funcions very well, but when I make a sum calcualtion I get not the right results. Monarch only calcualtes a value which exists in the Lookup file and is shown in the table screen of monarch.

        • Calculation of Summary Fields
          Grant Perkins

          Hi Oli,

           

          For your first question;

           

          All of the fields you want to perform calculations for must exist in the table.

           

          The summary can then use any NUMERIC fields to perform calculations which summarise the numbers. The default "Aggregation" is simply to SUM the numbers. However there are other possiblities for displaying alternative presentations of the values by changing the properties for the way the field is used in the summary definition. So you might have a version of a numeric field disp-laying its actual value summaries and another version of the same MEASURE displaying its value as a percentage of some other aggregation.

           

          Version 7 has some extensive possibilities for these displays including user defined formulas.

           

          There are similar possibilities if you are using the COUNT measure for the number of records - the results can be displayed as a COUNT, as a  percentage of something and as various other regularly used representations.

           

          Do you have a specific requirement that you can outline to us?

           

          As for the calculation on external lookup fields, I have not noticed a problem but then I do not remember many times when I use an Excel lookup for a numeric value and then a calculation.

           

          I will try it out at a simply level to see what happens.

           

          Do you always see the problem or does it only happen on some occasions?

           

           

          Grant

           

           

            Originally posted by Oli:

          Hi,

          I use Monarch Pro 7

          I try to make further calculation of summary fields. If I'm in the summary window there is, so far I can see, no possibility to insert/calculte calculated fields like in the tabble screen.

          Is there a way of proceeding calcualtions in the summary window?

           

          A second questions concerns proceeding calcuations within Monarch of values coming to Monarch via External Lookup. I use a Excel file and the lookup funcions very well, but when I make a sum calcualtion I get not the right results. Monarch only calcualtes a value which exists in the Lookup file and is shown in the table screen of monarch. /b[/quote]

          • Calculation of Summary Fields
            Bruce _

            Oli

             

            I use Excel lookups all the time to make calculations in the summary field. The only thing I can think of, is that the excel database is bringing the numeric fields into Monarch as text.

             

            The best bet would to make sure the data comes in as numeric, but you can use val() as a work around to get numerics. If you can see it as a numeric in the table, you can use it in the summary to do summary-calculations.

             

            Good luck

            • Calculation of Summary Fields
              OJGraf _

              Thanks for your answers,

               

              @Grant

              Here is the exmaple, I'm in the table view:

              fyi:

              A=normal column with figures (numeric)

              B=External Lookup value, figures, but if there is no lookup-value then "0" appeared (as numeric)

              3 colums (* means the next colum begins)

              A * B * Calculated Field Result

              10 * 0 * 0  (fault) 

              25 * 15 * 40 (right result)

              82 * 8 * 90 (right result)

              112 * 0 * 0  (fault)

               

              You can see that, if the lookup value shows nill, then the sum-calculation gives out the wrong calculated value "0"

               

              To better display my problem, what can I do to visualize much better within the forum.

               

              greetings

              Oli

              • Calculation of Summary Fields
                Grant Perkins

                Oli,

                 

                I am going to guess that the zeros, or some of them, are actually NULLs displayed as zeros (Have a look at your Options Table for View options and see how it is set).

                 

                When Monarch tries to calculate a numeric field which has a zero value it will not produce a numeric result or the field will be imported as if it is a text field.

                 

                If that IS the problem then Bruce's suggestion should fix it for you. Set up a calculated field for the imported value to create a numeric value - either the found value or a numeric zero if there is no match on the lookup. If the problem is just NULLS where no match then the ISNULL() function should allow you to build an IF() function formula that 'converts' the NULL to a numeric zero value.

                 

                If(IsNull()=1,0,[LookupField])

                 

                should give you the rsults you need.

                 

                The example will make a NULL entry into a 0 value. If the entry is NOT NULL it will use the existing value.

                 

                I hope this diagnosis is helpful. Please let us know if it is not and we can look for some other cause.

                 

                Grant

                • Calculation of Summary Fields
                  OJGraf _

                  thanks grant and bruce

                   

                  I tried the formula of you grant. And it works now, many thanks.

                   

                  greetings Oli

                  • Calculation of Summary Fields
                    OJGraf _

                    Is there no way for making further calculations with summary fields ? I have the problem, that I'd like to display "0", if the value calculated in the summary field is negative.

                     

                    greetings,

                    Oli

                    • Calculation of Summary Fields
                      Grant Perkins

                      Originally posted by OJGraf:

                      Is there no way for making further calculations with summary fields ? I have the problem, that I'd like to display "0", if the value calculated in the summary field is negative.

                       

                      greetings,

                      Oli /b[/quote]Oli,

                       

                      I do not know of a way to do that and I am not sure it would be a good thing to do from an audit point of view!

                       

                      You could make the number 'disappear' by setting the colouring for the field properties to have background and text the same colour for values less than 0 - maybe.

                       

                       

                      Grant