Reports - based on Last Week?

Hi Jessica Linstrot,

I am not sure how you would do that with the reports-builder but you can use Advanced Reports (support.sugarcrm.com/.../index.html) with a custom query:

SELECT
 (curdate() - INTERVAL((WEEKDAY(curdate()))+7) DAY) as monday_last_week.
 (curdate() - INTERVAL((WEEKDAY(curdate()))+1) DAY) as sunday_last_week

So your report query could look something like:

SELECT * FROM leads
WHERE 
  deleted = 0
  AND date_entered >= (curdate() - INTERVAL((WEEKDAY(curdate()))+7) DAY)
  AND date_entered < (curdate() - INTERVAL((WEEKDAY(curdate()))+1) DAY)
ORDER BY date_entered ASC

I used Leads for easy testing in MySQL Workbench since we create leads daily.

FrancescaS

Hi Francesca Shiekh,

Thanks for your information.  Unfortunately, I don't know how to do SQL queries.  Is there any way you know of to capture this information in the regular Reports module?

Thanks!

I will give it some more thought but I can't think of a good way to do this without coding or MySQL reports.

agree, the operators are very limited........I've had an enhancement request in since 2021......

I agree with this and have just asked our partners to request this with Sugar 'Last Week',  'This week' and 'Next week'.  Some of our KPIs are weekly and agree none of the other #days are of any use.

One option offered was to use a monthly filter and group by weeks but doesn't have the same impact as Last, This & Next Week.

Please add these filter options Sugar, I'm sure its not a big piece of work but a massive improvement for the users!!

FYI -

I find that the best way to request a change is to file a Case with Sugar Support.

They will log it as an "Idea" and the more people ask for something the more likely you are to have that idea implemented.

FrancescaS

Looks like an enhancement request has been in for a while....  other CRM's i have used have this in their selections and have done for many years.

Hi Jessica,

Assuming its not a scheduled report I have used 'Is Between' before which works fine.  Doesn't work for scheduled though and your request for last week and even next week would be great to have.

Lou 

We asked about this in 2016, on Sugar Support Case #267440, and got referred to "Issue & Ideas" #52123 Add options to filter by "this week" and "last week" instead of "next 7 days" and "last 7 days" which has a create date of April 2012. 

I have had someone tell me that these might happen soon. Let's see if this particular forward looking statement has substance.

Ben.