4 Replies Latest reply: Sep 9, 2014 11:18 AM by N G RSS

    Using InWeek with set analysis

    N G

      HI All,

       

      Is it possible please to use set analysis with the function 'inweek' ? i.e. I have this to convert ..

       

       

      sum( if ( inweek (MyTxnDate, today(2), -52 and DayOfWeek=Mon, Sales))

       

      ........so the 'DayOfWeek=Mon' converts to set analysis easy enough, but when I try to convert the inweek, any syntax I use throws up an error. I am trying to convert as the calculation currently is so sloooooowwwww.

       

      Thanks in advance

       

      Bob

        • Re: Using InWeek with set analysis
          Vlad Gutkovsky

          Looks like you're missing a ) parentheses. But I would suggest you save yourself a lot of headaches and just create a variable. Looks like you're trying to get the Monday that occurred 52 weeks ago. Something like this should work:

           

          vRelevantSalesDate

          =weekstart(today(2)-52*7))

           

          Expression

          sum({<MyTxnDate={'$(vRelevantSalesDate)'}>}Sales)

           

          Regards,

          Vlad

            • Re: Using InWeek with set analysis
              N G

              HI Vlad,

               

              Thankyou - your formulae would indeed for just the monday, but then if I need the whole week i.e.

               

              sum( if ( inweek (MyTxnDate, today(2), -52), Sales))

               

              .. you are not aware of a 'set analysis' logic that could work with this please ?

               

              Thanks

              Bob

                • Re: Using InWeek with set analysis
                  Vlad Gutkovsky

                  Just make a start and end date variable.

                   

                  vRelevantSalesStartDate

                  =weekstart(today(2)-52*7))

                   

                  vRelevantSalesEndDate

                  =weekend(today(2)-52*7))

                   

                  Expression

                  sum({<MyTxnDate={">=$(vRelevantSalesStartDate)<=$(vRelevantSalesEndDate)"}>}Sales)

                   

                  I'm not sure of the exact "Monday" complexities that you need to include, but whatever you need to do can just be done in these 2 variables. That's why using variables with date set analysis is so nice--saves you a lot of work with maintenance.

                   

                  Regards,

                  Vlad