topic Re: Displaying last week's data in QlikView

Displaying last week's data

Anonymous — Mon, 28 Sep 2015 11:45:24 GMT

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:

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

Thanks a lot!

Re: Displaying last week's data

Clever_Anjos — Mon, 28 Sep 2015 11:48:53 GMT

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

should fix this

Re: Displaying last week's data

Anonymous — Mon, 28 Sep 2015 11:58:41 GMT

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.

Re: Displaying last week's data

nagarjuna_kotha — Mon, 28 Sep 2015 11:59:31 GMT

Can u share some sample data ?

Re: Displaying last week's data

Anonymous — Mon, 28 Sep 2015 12:02:37 GMT

Unfortunately I can't, sorry.

Re: Displaying last week's data

Clever_Anjos — Mon, 28 Sep 2015 12:02:52 GMT

Would you mind sharing your app?

Re: Displaying last week's data

daveamz — Mon, 28 Sep 2015 12:03:40 GMT

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

Re: Displaying last week's data

Anonymous — Mon, 28 Sep 2015 12:27:14 GMT

Not sure butTry this

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

Re: Displaying last week's data

Anonymous — Fri, 02 Oct 2015 15:06:00 GMT

Thanks! This worked perfectly!