Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
[Cancelation Date]= {'>$(=date(today(0)-8)) < $(=date(today(0))) }
should fix this
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.
Can u share some sample data ?
Unfortunately I can't, sorry.
Would you mind sharing your app?
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
Hi
Not sure butTry this
=Count({<CancellationDate={'>=$(=Weekstart(Today()-6)) <=$(=WeekEnd(Today()-6))'}>}Subscription)
Thanks! This worked perfectly!