Compare count of this week vs last week

[peterderrington]

Hi There, 

I'm hoping this will be a fairly easy one for you guys.

I'm trying to create a simple straight table that shows the count of compliance against the count for 7 days ago (ultimately i then want to be able to show a little up green or red down arrow next to it)

I can see the current count with this expression:

Count([Name])-Count({<[Name]={"Unknown"}>}[Name])

But when i try this (which i thought it might be) it correctly shows the date as 7 days prior but the count is empty.

=count(>=$(=(Date(Today()-7)))
=Count([Name])-Count({<[Name]={"Unknown"}>}[Name])

[peterderrington]

So i still can't seem to get it to do this correctly.

Think its just an issue with the syntax of the expression.

I've had a play around (hence whys there's several extra columns in the pic) but to no avail.

Help.....

[tincholiver]

please share the data table that you are loading

[peterderrington · Accepted solution]

Its ok,

With the help of our supplier a solution has been created:

In the script we wrote this:

Let vLastWeekStart = Date(WeekStart(Today())-7,'DD/MM/YYYY');
Let vLastWeekEnd = Date(WeekStart(Today())-1,'DD/MM/YYYY');


Let vLastWeekStart2 = Date(WeekStart(Today())-14,'DD/MM/YYYY');
Let vLastWeekEnd2 = Date(WeekStart(Today())-8,'DD/MM/YYYY');

and then:

Date#(Date([Date/Time left ward],'DD/MM/YYYY'),'DD/MM/YYYY') as [Date/Time left ward],

If(Date#(Date([Date/Time left ward],'DD/MM/YYYY'),'DD/MM/YYYY') >= '$(vLastWeekStart)',
if(dATE#(Date([Date/Time left ward],'DD/MM/YYYY'),'DD/MM/YYYY') <= '$(vLastWeekEnd)', '1','0')) AS [Last Week Flag],

If(Date#(Date([Date/Time left ward],'DD/MM/YYYY'),'DD/MM/YYYY') >= '$(vLastWeekStart2)',
if(dATE#(Date([Date/Time left ward],'DD/MM/YYYY'),'DD/MM/YYYY') <= '$(vLastWeekEnd2)', '1','0')) AS [Previous Week Flag],

Which meant we just needed to add this expression into the table to show the total counted Last Week:

=Count({<[Questions Complete]={'Yes'}, [Last Week Flag]={'1'}>} [Questions Complete])

I can't say I fully understand all the syntax but it works (and I will keep working on it so that I can 'get' what's been done.

Thank you so much to everyone that's helped, you're all fab!