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

# Minus 7 days

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

• ###### Re: Minus 7 days

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

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

• ###### Re: Minus 7 days

Does:

Count(

{\$

<

Delivery_Date={'>=\$(=WeekStart(Week_Beginning)-7)'},

Delivery_Date={'<=\$(=WeekStart(Week_Beginning)-1)'}

>

}

Customer_ID)

work? (am assuming the field you are filtering is called Week_Beginning

• ###### Re: Minus 7 days

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

Hiya

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?

• ###### Re: Minus 7 days

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

Hiya

still no joy. cannot load app sorry but

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