4 Replies Latest reply: Sep 5, 2017 2:38 AM by Liv ma RSS

    Relational operators as set modifiers

    Liv ma

      hello

       

      I have the following date structure: date, client, sales

       

      suppose I want to calculate the sales for the period after a certain date (let's say after 20 nov 2016)

       

      I have tried to use sum({<date>42694>}Sales) but it does not work, so is there an alternative way to do this?

       

      thank you very much

      LM

        • Re: Relational operators as set modifiers
          Marcus Sommer

          The syntax isn't quite right. Try this one:

           

          sum({< date = {">42694"} >} Sales)

           

          - Marcus

          • Re: Relational operators as set modifiers
            Anil Babu Samineni

            How is your date format looks like? Is that is in number format or Date format. If date format it won't work for numbers.

             

            date(date) as date

             

            Expression:

            sum({< date = {"> $(=Date(...))"} >} Sales)

            • Re: Relational operators as set modifiers
              Sam Grounds

              Hi,

               

              Marcus' answer will give you exactly that.

               

              To expand on this a little bit, it depends on what your data format is. If your date is stored as a number then 42694 will work, however if it is stored as a date, then you will need to do it like this...

               

              sum({$<date={'>20/11/2016'}>}Sales)

               

              (note: use single quotes around the date.)

               

              Or if you're using a dynamic date, something you want the user to be able to specify, the simply pre-calculate a variable with something like this...

               

              sum({$<date={'>$(vDate)'}>}Sales)

               

              The $(vDate) would reference a variable called vDate that you can put into an on-screen object and allow them to change or you can edit it directly with the variable overview (CTRL + ALT + V), or specify it in the load script with...

               

              LET vDate = '20/11/2016';

               

              Or even

               

              LET vDate = Date(Today()-1,'DD/MM/YYYY');

              (for yesterday).

               

              If it's numerical format, then use Date#().

               

              Hope this helps.

              Sam

              • Re: Relational operators as set modifiers
                Liv ma

                thank you all, your solutions worked - I obviously wasn't using the correct syntax and on top of that I also had a different formatting of the date.

                 

                great community, thank you once again.