4 Replies Latest reply: May 15, 2014 10:06 AM by Data Kruncher RSS

    Mental Block

    Chickenman _

      We have some "overlapping" data sets. Trying to create a calculated field such that if the field in one column is not blank and[/U] the date from another falls in a specific range, then bring in value from column of sales values.

       

      Seems possible, and think have done before, but have gone daft recently. Please help. :confused:

       

      thx,

      CM

        • Mental Block
          Data Kruncher

          If(CharField<>"" .AND. ((MyDate >= StartDate) .AND. (MyDate <= EndDate)),SalesValue,0)[/code]Sound about right?

           

          Kruncher

            • Mental Block
              Grant Perkins

              You know you could try searching your models for something that looks about right using the Used Defined Functions search capability. If you can think of a potential connection to what the model might contain a few minutes might be time well spent - then you can just use and adapt what you had previously.

               

              Grant

                • Mental Block
                  Chickenman _

                  Kruncher, perfect. Had too many - too few? - parentheses going here and recognized I was in trouble when I started considering nested If constructions...:eek:

                   

                  Grant, actually did glance through models but the nearest likely candidates are on the MDP server which was unavailable.

                   

                  Thank you both for the quick replies.

                   

                  CM

                    • Mental Block
                      Data Kruncher

                      Glad to hear that you're on your way with it CM.

                       

                      There are two approaches that I like to use when building formulas with a number of parentheses:

                       

                      Sum the ( and ) characters. That is, +1 for ( and -1 for ), so that you always wind up with 0. If you don't and the number is positive, you have too many (. If negative, too many ).

                      To help avoid that problem in the first place, immediately type the () together and them fill in the details. While it can work, I do find this a bit tricky if it's all packed in tight.

                      /LIST

                      Also, take advantage of Monarch's flexibility by adding white space and putting different calculations on different lines. We tend to get accustomed to the look of formulas that have no room to breathe, ala Excel, which IMO doesn't help at all when things get complicated.

                       

                      HTH,

                      Kruncher