4 Replies Latest reply: Nov 30, 2016 8:30 AM by Elaine Woods RSS

    if greater than statement with text field

    Elaine Woods

      I am trying to do a sum on a quantity if the week of an opportunity is greater or equal to current week.

      where rol_week= Week xx and Current_ROL_Week = Week 09

      sum({<rol_week= {">=Current_ROL_Week"}>}quantity)

       

      Tried several iterations, so I'd take any suggestions.

       

      thanks,

      Elaine

        • Re: if greater than statement with text field
          Sunny Talwar

          Do you have only one value for Current_ROL_Week or does the value change based on dimension? If there are multiple values, I think you will have to use Aggr() function with if statement instead of using set analysis to get this done. Would you be able to provide few rows of data with the expected output to help us understand what you are trying to do?

           

          Best,

          Sunny

          • Re: if greater than statement with text field
            kalyani vintha1

            Maybe try like this.,

             

             

            sum({<rol_week={>=$(=weekxx)  <=$(=currentweek)}>} quantity)

            • Re: if greater than statement with text field
              Marcus Sommer

              With strings are no "><" comparings possible - only "=" or "like". This meant in your case that you would need to create a list of values for this data-set comparing within the set analysis, maybe something like this:

               

              chr(39) &

              concat(distinct if(num(keepchar(Current_ROL_Week, '0123456789')) <=

                                          num(keepchar(rol_week, '0123456789')), rol_week), chr(39) &', ' & chr(39)) & chr(39)

               

              Better and a lot easier than this would be to double your week-field within the datamodel with pure numeric content like:

              1,2,3,4..... And this is a general recommendation to use only pure numeric fields (without any formattings) for calculating or comparing.

               

              - Marcus

              • Re: if greater than statement with text field
                Elaine Woods

                I appreciate the quick responses.  Someone at work sent me this later Monday night and it worked.

                 

                Sum({<Rol_week = {"=Rol_week follows '$(=CurrentWeek)'"}>}quantity)


                thanks,

                Elaine