8 Replies Latest reply: Nov 2, 2015 10:00 AM by Kevin Case RSS

    Minus 7 days

    Joanna Seldon

      Hiya

       

      I wish to select a date from Week_Beginning and it returns customers, only with delivery dates in the previous week.

       

      e.g if I select 13/09/2015

       

      I want only to return customers who have a delivery date 6-12 September

       

      I have wrote this expression

       

      count({$< Delivery_Date -={">=$(=Date(WeekStart(max({$}Date))-7))"}>}Customer_ID)

       

      all customers remain

       

       

      and removing the - like this  count({$< Delivery_Date ={">=$(=Date(WeekStart(max({$}Date))-7))"}>}Customer_ID)

       

      gives me null

       

      please help

        • Re: Minus 7 days
          Kevin Case

          Joanna,

           

          Hopefully I can help or at least get you closer to a resolution.  I am going to assume that the user can select any day of the week.  So, if your user was to select 16/9/2015 (a Wednesday) then you will want to select everything that has a Delivery_Date between 6/9/2015 and 12/9/2015 which are the beginning and ending dates of the previous week.

           

          To get the start of the previous week would be something like this:  WeekStart(YourSelectedDateGoesHere))-7

          To get the end of the previous week would be something like this: WeekStart(YourSelectedDateGoesHere))-1

           

          If your user can select ANY date, you probably want to check for Delivery Dates that are between the start and end of the previous week otherwise you will get everything >= to the start of the previous week.

           

          So when we put this together in your set analysis, it may go something like this:

           

          Count({$<Delivery_Date={">=$(=WeekStart(YourSelectedDateGoesHere))-7)<=$(=WeekStart(YourSelectedDateGoesHere))-1)"}>}Customer_ID)

           

          If you have a variable as your selected date, then it would be something like this:

           

          Count({$<Delivery_Date={">=$(=WeekStart($(vSelectedDate))-7)<=$(=WeekStart($(vSelectedDate))-1)"}>}Customer_ID)

           

          Hope this helps.

           

          Kevin

            • Re: Minus 7 days
              Joanna Seldon

              Hiya

               

              sorry for the delay, this does not work,

               

              I have had a play removing brackets ect.

               

              is there a way of using  something like

               

              Delivery_Date =  getselectedfield WeekStart(Week_Beginning) - 7    ) <=$ getselectedfield WeekStart(Week_Beginning) - 1

               

              please help

            • Re: Minus 7 days
              Stefan Wühl

              If you (your user) can make selection on calendar field other than Delivery_Date, like Date, you need to clear all these fields in set analysis to avoid incompatible selections:

               

              =count({$< Delivery_Date ={">=$(=Date(WeekStart(max({$}Date))-7))<$(=Date(Weekstart(max(Date))))"}, Date = >} Customer_ID)

                • Re: Minus 7 days
                  Joanna Seldon

                  Hiya

                   

                  no joy, and yes I am filtering on Week_Beginning, please help,

                   

                  when I use above the system says set analysis is ok, however Week_Beginning is showing black, when if a field is selected it should go orange?

                   

                  please help

                    • Re: Minus 7 days
                      Stefan Wühl

                      Not sure if I understand, could you upload a sample app?

                       

                      Maybe you just need to replace Date with Week_Beginning (if Week_Beginning has a numerical date representation):

                       

                      =count({$< Delivery_Date ={">=$(=Date(WeekStart(max(Week_Beginning))-7))<$(=Date(Weekstart(max(Week_Beginning))))"}, Week_Beginning= >} Customer_ID)


                      Remember to clear all calendar fields the user may select in in your set expression.


                      Maybe you also need to adapt the Date() format code to match your Delivery_Date format.


                      But it's kind of guessing without knowing your data model, field values formats and what UI objects are involved.

                        • Re: Minus 7 days
                          Joanna Seldon

                          Hiya

                           

                          still no joy. cannot load app sorry but

                           

                          in the data load

                           

                          Date(Delivery_Date) as "Delivery_Date",

                           

                          Date(Week_ Beginning ) as "Week_ Beginning",

                           

                          week beginning is just shown as

                           

                          e.g 05/01/2015

                          12/01/2015, and so forth,

                           

                          Delivery date is just shown as

                           

                          01/01/2015, 02/01/2015 ect

                           

                          please help