Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fervendo89
Contributor II
Contributor II

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

fervendo89
Contributor II
Contributor II
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
Partner

Can u share some sample data ?

fervendo89
Contributor II
Contributor II
Author

Unfortunately I can't, sorry.

Clever_Anjos
Employee
Employee

Would you mind sharing your app?

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

rgvavihs
Specialist
Specialist

Hi

Not sure butTry this

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

fervendo89
Contributor II
Contributor II
Author

Thanks! This worked perfectly!