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: 
Anonymous
Not applicable

Displaying last week's data

Hi everyone,

I have a table and want to display data from last week. This table reloads everyday at 10:00 and it's set to be sent every Monday after the reload.

Today is September 28th, so I'd like to display everything that occurred between September 21st and 27th. In other words, I want to display all users who canceled their subscription at any point during last week.

I'm currently using this expression:

=Count({<[Cancelation Date]= {'>$(=date(today(0)-8))'}, [Action Type ID] = {1,2,3,4,22,26} ,[Merchant ID] = {2076,10017}>} [Subscription ID]) 

The problem is that I'm getting data from TODAY (Sep 28th), see image below:

Captura de pantalla 2015-09-28 a las 1.43.41 PM.png

Can anyone help? I'd like to get rid of data outside the range.

Thanks a lot!

1 Solution

Accepted Solutions
daveamz
Partner - Creator III
Partner - Creator III

Hi Ferando,

I would create a flag like:

LOAD

.....

[Cancelation Date],

InWeek([Cancelation Date],Today(),-1) as [_LastWeek Cancelation Date]

....

Then I would use the flag in the set analysis:

=Count({<[_LastWeek Cancelation Date]= {'-1'}, [Action Type ID] = {1,2,3,4,22,26} ,[Merchant ID] = {2076,10017}>} [Subscription ID])

Best regards,

David

View solution in original post

8 Replies
Clever_Anjos
Employee
Employee

[Cancelation Date]= {'>$(=date(today(0)-8)) < $(=date(today(0))) }

should fix this

Anonymous
Not applicable
Author

Hi!

Thanks for the help, but still not working.

I changed the expression to Count({<[Cancelation Date]= {'>$(=date(today(0)-8)) < $(=date(today(0)))'}, [Action Type ID] = {1,2,3,4,22,26} ,[Merchant ID] = {2076,10017}>} [Subscription ID]) 

but data from today is still being displayed.

nagarjuna_kotha
Partner - Specialist II
Partner - Specialist II

Can u share some sample data ?

Anonymous
Not applicable
Author

Unfortunately I can't, sorry.

Clever_Anjos
Employee
Employee

Would you mind sharing your app?

daveamz
Partner - Creator III
Partner - Creator III

Hi Ferando,

I would create a flag like:

LOAD

.....

[Cancelation Date],

InWeek([Cancelation Date],Today(),-1) as [_LastWeek Cancelation Date]

....

Then I would use the flag in the set analysis:

=Count({<[_LastWeek Cancelation Date]= {'-1'}, [Action Type ID] = {1,2,3,4,22,26} ,[Merchant ID] = {2076,10017}>} [Subscription ID])

Best regards,

David

Anonymous
Not applicable
Author

Hi

Not sure butTry this

=Count({<CancellationDate={'>=$(=Weekstart(Today()-6)) <=$(=WeekEnd(Today()-6))'}>}Subscription)

Anonymous
Not applicable
Author

Thanks! This worked perfectly!