Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joeybird
Creator III
Creator III

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

please help

8 Replies
kevincase
Creator II
Creator II

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
Creator III
Creator III
Author

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

Not applicable

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
MVP
MVP

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
Creator III
Creator III
Author

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
MVP
MVP

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
Creator III
Creator III
Author

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
Creator II
Creator II

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.