7 Replies Latest reply: May 15, 2014 9:58 AM by EileenG _ RSS

    If-Calcualtion

    Oli _

      I've got following problem:

       

      I've got 1 costcenter for different customers:

      [font="courier"]CC     customer value1      value2

      1200     A     -100          0

      1200     B     50          0

      1200     C     20          0[/font][/quote]Because that the sum over the costcenter (CC) is negative I try to generate (calculate) value2=0 for every detailed line of the costcenter (like you can see in the column value2.

       

      How can I get this result ?

       

      Kind regards

      Oliver

        • If-Calcualtion
          Grant Perkins

          Hi Oli,

           

          Here is one way to do it.

           

          Create a Summary with the CC, customer and value columns and column totals.

           

          Export the summary to a new file.

           

          Open the new file and model the 3 columns with the Total for the Value column as a footer which will be added to each of the detailed lines.

           

          Now do the calculation using the footer field as the starting point for a new calculated field using an IF() formula .

           

          IF(FOOTER_FIELD<0,0,FOOTER_FIELD) should do it.

           

          HTH.

           

          Grant

          • If-Calcualtion
            Oli _

            Hi Grant,

            I try to avoid to over a second model. Is there no other option to do it within one model ?

             

            Kind Regards

            Oli

            • If-Calcualtion
              Grant Perkins

              Originally posted by Oli:

              Hi Grant,

              I try to avoid to over a second model. Is there no other option to do it within one model ?

               

              Kind Regards

              Oli /b[/quote]A calculated field within a record can, by definition, only refer to the data in that record field group plus whatever external information can be read in on a record by record basis.

               

              If you are using a summary you can probably define something that would give an alternate result based on a calculation which could make use of information in the individual detail records that make up a summary line - in other words would work at different drill down levels.

               

              But what you asked for is a calcuated field based not on a record or records within and individual key but on the summary calculation of total value.

               

              Since Monarch has to calculate each line value (including the field that would decide if the total value should be greater than zero) before it can calculate the summary total line I cannot think of a way work the summary total back into a detail line or to use the result to make a conditional calculation of the sort you require.

               

              I hope any numbers you produce from the by the substitution are not going to find their way into any audit report activity! That could be rather dangerous.

               

              I may be wrong, so if anyone has a way to do this I would be very keen to hear about it.

               

              Have fun.

               

               

              Grant

              • If-Calcualtion
                Oli _

                Hi Grant,

                thanks for your help.

                 

                I try to handle this within one model in the table view after failed to make within the summary windows. Perhaps it is possible ?

                 

                It is possible by using an if-condition in that way, but...

                [font="courier"]IF((((=1200 .And. customer="A" .And. )+ (=1200 .And. customer="B" .And. )+

                (=1200 .And. customer="C" .And. )))<0;0;[value1])[/font][/quote]... this isn't working correct. Monarch doesn't use the if condition. It shows the unchanged figures of .

                 

                I get the "0" by using this condition the detailed lines: 

                [font="courier"]IF(=1200 .And. customer="A" .And. <0;0;[value1])[/font][/quote]but if I combine like explained above the results are unchanged.

                 

                Is there a possibility to give Monarch the right conditions, that the calculation is correctly done ?

                 

                Oli

                • If-Calcualtion
                  Grant Perkins

                  Hi Oli,

                   

                  Your second IF formula is fine for processing any lines where the CUSTOMER value is A and the value1 field is less than 0, which is why it works.

                   

                  I am surprised that the first one is accepted in the field edit but in any case I cannot see how it could give you what you want.

                   

                  The calculated field can only work for one table row at a time.

                   

                  IN any row your CUSTOMER field value nmight be A or B or C and so an IF formula that is written to provide rules for each of these cases IN THAT ROW is, potentially, valid.

                   

                  However only data in that row wil be processed, not data for the entire report. So, in effect, your second formula is the only one that would be used even if the formula for multiple values of CUSTOMER.

                   

                  However the rule you wish to apply seems uncertain.

                   

                  If any row with a negative VALUE1 needs to be 0, then your IF formula does not need to consider the customer field at all.

                   

                  My original interpretation of you need was that the VALUE2 values should be zero IF AND ONLY IF the entire cost centre calculation for VALUE1 for all the lines listed calculated to less than 0.

                   

                  If that is what you want I don't think you can do that as part of a single field calculation on a single line. The process will not be able to calculate the total value for any field (and not in a table value anyway) until all the rows have been processed. So to get a row by row calculated field which makes ues of the results of calculating all rows would require the process to run through the data twice. The second time it wouold need to make use of the calculated result of the first process run.

                   

                  The 2 stage process I proposed before is doing exactly that in effect. The summary provides the calculations for the total(s) and the second stage of the process allows that value to be used to make a decision about the value to be presented in VALUE2.

                   

                  There is a little more potential in a SUMMARY for showing values based on calculations that use summarised data but I still don't think you will have the option to make use of the totals to decide whether to display the calculated total or a zero value. I could be wrong, someone may have found a way to do that in a single model.

                   

                   

                  Grant.

                  • If-Calcualtion
                    EileenG _

                    Originally posted by Oli:

                    I've got following problem:

                     

                    I've got 1 costcenter for different customers:

                    [font="courier"]CC     customer value1      value2

                    1200     A     -100          0

                    1200     B     50          0

                    1200     C     20          0[/font][/quote]Because that the sum over the costcenter (CC) is negative I try to generate (calculate) value2=0 for every detailed line of the costcenter (like you can see in the column value2.

                     

                    How can I get this result ?

                     

                    Kind regards

                    Oliver /b[/quote]

                    • If-Calcualtion
                      EileenG _

                      I have this same problem and do not want to have to export then bring back in. Has anyone found a fix for this?

                       

                      Thanks