2 Replies Latest reply: Mar 8, 2016 12:18 PM by Deepanshu Chamoli RSS

    Use of Variable

    Deepanshu Chamoli

      Hello All

       

      Kindly help me in the below logic.

       

      I have created something not sure it is correct or not.

       

      If the difference between Today Date and First day of the Month is Less than 6 than it picks the Last day of the previous month else Today date.

       

      Formula I wrote is:

       

      Date_value = IF((TODAY() - MONTHSTART(TODAY()))<6, DATE(MONTHSTART(TODAY())-1), TODAY())

       

      I need the below two things as:

       

      1. received_date >= Date_value - 3

      2. received_date < Date_value - 3

       

      Can someone suggest me how I can use this in set analysis

       

      Thanks

      Deepanshu

        • Re: Use of Variable
          Stefan Wühl

          You can create another variable, subtracting the 3 days and formatting the date same way as received_date (assuming its format is the default script format):

           

          Date_value_threshold:

          =Date( Date_value -3)

           

          If it's not in the default format,add a format code to Date() function:

          =Date( Date_value -3,'MM/DD/YYYY')

           

          Then in your expressions, maybe something like

           

          =Sum({<received_date = {">=$(Date_value_threshold)"}>} Value)

           

          resp.

           

          =Sum({<received_date = {"<$(Date_value_threshold)"}>} Value)