5 Replies Latest reply: Sep 26, 2013 2:30 PM by Thom Mumaw RSS

    Set Analysis Expression....

    Thom Mumaw


      I am trying to use set analysis to sum a dollar value if the document create date is between two other dates, however it says there is nothing wrong with the syntax it's not working.  Does anyone see anything wrong with my statement?  All dates have been converted to numeric fields.

       

       

      sum({<Contract_Create_Date_Num = {">=[Camp_Start_Date_Num]<=[Camp_end_Date_Num]"}>} [Subtotal 1])

       

      Thanks

       

        • Re: Set Analysis Expression....
          Luciano Assad

          Hi, you must peek only one value, not all of them.


          So you need to use max, min, only, etc.


          Use like this:

          sum({$<Contract_Create_Date_Num = {">=$(=max([Camp_Start_Date_Num]))<=$(=max([Camp_end_Date_Num]))"}>} [Subtotal 1])


          • Re: Set Analysis Expression....
            Oleg Troyansky

            Thom,

             

            I can only guess that the reason is that there are multiple values available for your start and end dates, and therefore the expression cannot be rendered globally. To overcome that, I'd try to look for a level of detail that can guarantee that the two fields are unique. For example, if those fields are unique per Contract, then I'd reformat your condition like this:

             

            sum({<Contract_ID = {"Contract_Create_Date_Num>=[Camp_Start_Date_Num] and Contract_Create_Date_Num<=[Camp_end_Date_Num]"}>} [Subtotal 1])

             

            if the two fields are still not unique per contract, you may have to use min() or max() to arrive to the single value.

             

            The other way of solving it would be calculating a flag in the script (assuming that the three date fields are easily accessible) and assigning 1 to the contracts that fit your condition. Then, the set analysis becomes very simple:

             

            sum({<Contract_Date_Flag = {1}>} [Subtotal 1])

             

            cheers,

             

            Oleg Troyansky

            www.masterssummit.com

              • Re: Set Analysis Expression....
                Thom Mumaw

                Hi Oleg,

                I have a group of contracts which were created on different days. I have a group of campaigns which has a start dates and an end dates. I need to see if the contract create date falls within the valid start and end dates for the
                campaign. When I use this ({<Contract_Create_Date_Num = {">=41449 <=41479"}> } [Subtotal 1]) (as a test it works fine).  When I try and add the logic you and the rest of the forum provided it doesn’t work.  I’m confused…..

                Thanks
                Thom

                 

                 

                 

                 

              • Re: Set Analysis Expression....
                mayilvahanan ramasamy

                Hi

                 

                Try like this

                 

                sum({$<Contract_Create_Date_Num = {">=$(=max([Camp_Start_Date_Num]))<=$(=max([Camp_end_Date_Num]))"}>} [Subtotal 1])

                • Re: Set Analysis Expression....
                  Thom Mumaw

                  Oleg,

                  Thanks for your help.  After reviewing the model, a few left joins here and a resident table here I was able to use the simple method.  Made my life much easier.

                  Thanks

                  Thom

                   

                  sum({<Contract_Date_Flag = {1}>} [Subtotal 1])

                   

                  P.S. Thanks to everyone else to for the suggestions.