Details about this thread
  • State Not Answered
  • Replies 9 replies
  • Subscribers 326 subscribers
  • Views 2743 views
  • Users 0 members are here

Reports - based on Last Week?

Jessica Linstrot

Hi Team,

I need to run a number of reports every Tuesday for activity the previous week.  When defining the filters, there is no Last Week option.  How can I run the report based on the previous week (Sun - Sat or Mon - Sun)? 

I don't want to use Last 7 Days, because then I miss out on last Monday and incorrectly include yesterday.  

I also don't want to use Last # Days (where # = 8), because this would incorrectly include yesterday.

I really appreciate any assistance you can give on this.

Jessica 

  • 0

    Hi ,

    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:

    Fullscreen
    1
    2
    3
    SELECT
    (curdate() - INTERVAL((WEEKDAY(curdate()))+7) DAY) as monday_last_week.
    (curdate() - INTERVAL((WEEKDAY(curdate()))+1) DAY) as sunday_last_week
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    So your report query could look something like:

    Fullscreen
    1
    2
    3
    4
    5
    6
    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
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

    FrancescaS

  • 0 in reply to Francesca Shiekh

    Hi ,

    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!

  • 0 in reply to Jessica Linstrot

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

  • 0

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

  • 0

    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!!

  • 0

    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

  • 0 in reply to Francesca Shiekh

    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.

  • 0

    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 

  • 0 in reply to Francesca Shiekh

    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.

    Ben Hamilton (more)