5 Replies Latest reply: Nov 2, 2016 6:44 PM by Mervyn O'Connell RSS

    Set-analysis, multiple static ranges

    Mervyn O'Connell

      Hi all,

       

      I've been using Denys Smakovskyi method of set analysis to ignore/use specific selections & adding static ranges. The issue I'm having is when I'm trying to use multiple conditions, I'm appending using the "+" set_operator (perhaps you know a better way?)

       

      This will run but ignores the second condition "[OrderStatus]" so returns the sum of all orders (regardless of OrderStatus) that were entered within the specified datetime range


      SUM(

      {$<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',','))>*1

      <[EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                                    <=$(=timestamp(firstworkdate(Floor(Today()),1)+12/24))"}>+

      <[OrderStatus]={"=$ (=22)"}> /* [22 = Active order] */

      }[OrdVal])

       

      Does anyone have an idea what I'm overlooking?

       

      Thanks

       

       

       



       

      Edited: formating

        • Re: Set-analysis, multiple static ranges
          Tresesco B

          May be like:

           

          SUM(

          {$<[OrderStatus]={"=$ (=22)"}, $(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',','))>*1

          <[EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                                    <=$(=timestamp(firstworkdate(Floor(Today()),1)+12/24))"}>

          }[OrdVal])

            • Re: Set-analysis, multiple static ranges
              Mervyn O'Connell

              Thanks for the suggestion (it executed successfully) but did not change the result.

               

              I can change the OrderStatus to "99999" (no such status) and the results remain the same. It's like it's not reading it at all.


              There is a function I dont understand and cannot find documentation on. In the original post it's referred to as "Set Analisys union with correct modified "1"" ie '['&$Field&']=',','))>*1 although the expression will not run without it so I left it in

            • Re: Set-analysis, multiple static ranges
              Sunny Talwar

              May be this:

               

              Sum({$<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',','))>*

              (1<[EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                        <=$(=timestamp(FirstWorkDate(Floor(Today()),1)+12/24))"}>+<[OrderStatus]={22})>} [OrdVal])

               

              or

               

              Sum({$<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',','))>*

              1<[EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                        <=$(=timestamp(FirstWorkDate(Floor(Today()),1)+12/24))"}, [OrderStatus]={22}>} [OrdVal])

                • Re: Set-analysis, multiple static ranges
                  Sunny Talwar

                  Or may be just one of these:

                   

                  Sum({$<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',',')), [EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                        <=$(=timestamp(FirstWorkDate(Floor(Today()),1)+12/24))"}>+<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',',')),[OrderStatus]={22}>} [OrdVal])

                   

                  or

                   

                  Sum({$<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',',')), [EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                        <=$(=timestamp(FirstWorkDate(Floor(Today()),1)+12/24))"}, [OrderStatus]={22}>} [OrdVal])

                    • Re: Set-analysis, multiple static ranges
                      Mervyn O'Connell

                      THAT'S IT!!!! stalwar1 you legend


                      Sum({$<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',',')),

                      [EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                        <=$(=timestamp(FirstWorkDate(Floor(Today()),1)+12/24))"},

                      [OrderStatus]={22}>

                      } [OrdVal])

                       

                      The entire expression needs to be within a single "< >" with commas delimiting the filters.

                       

                      Thank you so much