Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

fervendo89
New 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!

Tags (1)
1 Solution

Accepted Solutions
Highlighted
daveamz01
Contributor III

Re: Displaying last week's data

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

8 Replies
Employee
Employee

Re: Displaying last week's data

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

should fix this

fervendo89
New Contributor II

Re: Displaying last week's data

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.

Partner
Partner

Re: Displaying last week's data

Can u share some sample data ?

fervendo89
New Contributor II

Re: Displaying last week's data

Unfortunately I can't, sorry.

Employee
Employee

Re: Displaying last week's data

Would you mind sharing your app?

Highlighted
daveamz01
Contributor III

Re: Displaying last week's data

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
Valued Contributor

Re: Displaying last week's data

Hi

Not sure butTry this

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

fervendo89
New Contributor II

Re: Displaying last week's data

Thanks! This worked perfectly!