Multiple week-ending columns

[salleninsd]

Hello,

I am trying to have 5 columns in a table for the current week and the past 4 weeks and can't seem to get the syntax right using the following.

sum({$<[Visits.VisitDate]={">=$(=weekstart(Date(today(),'YYYY-MM-DD'))) <=$(=weekend(Date(today(),'YYYY-MM-DD')))"}>},[Visits.VisitBillableCategory]={"Billable Visit"}>}[Visits.VisitProductivityPoint]  )

The visit date format in my table is YYYY-MM-DD.

Here is what I am trying to get to:

Any assistance is much appreciated!

Scott

[sunny_talwar · Accepted solution]

Can you try this

Sum({$<[Visits.VisitDate] = {">=$(=Date(WeekStart(Today(), 4), 'YYYY-MM-DD')) <=$(=Date(Floor(WeekEnd(Today())), 'YYYY-MM-DD'))"}, [Visits.VisitBillableCategory] = {"Billable Visit"}>} [Visits.VisitProductivityPoint])

[salleninsd]

Wow, thanks for the quick response, Sunny!  This is working, but I want to be sure I understand... is the 4 for 4 weeks prior, so I would replace that with a 3 for 3 weeks prior?  I am trying to show 5 weeks running in the table columns.

[sunny_talwar]

Yes, 4 is for last 4 weeks... to see the date range, you can take the set analysis part in a text object to see what range is it showing based on today's date

='>=' & Date(WeekStart(Today(), 4), 'YYYY-MM-DD') & '<=' & Date(Floor(WeekEnd(Today())), 'YYYY-MM-DD')

[salleninsd]

Perfect!  Thank you soooo much for your help!  Have a terrific rest of the week!