Minus 7 days

[joeybird]

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

[kevincase]

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

[joeybird]

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

[#4]

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

[swuehl]

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)

[joeybird]

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

[swuehl]

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.

[joeybird]

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

[kevincase]

Joanna,

See attached sample.  User can select a date and it will return the count of customers that have a delivery date in the previous week.

If you select 9/9/2115, you should get a count of three customers with a delivery date in the previous week.