Conditional rollups based on date ranges with Sugar Logic?

Perhaps the rollupConditionalSum can help to solve your requirements, see the following posts for examples how to use it:

https://sugarclub.sugarcrm.com/explore/enterprise-professional/f/enterprise-professional-questions/3933/how-to-use-rollupconditionalsum-to-add-multiple-columns-with-criteria

https://sugarclub.sugarcrm.com/explore/enterprise-professional/f/enterprise-professional-questions/2805/rollupconditionalsum-in-ifelse-statement

Hi Harald,

Thanks for your reply. I'm already working with the rollupconditionalsum and count functions. The issue is that these functions apparently can only filter on "equal" operators, not > or <. So I can't get the total of opps which expected close dates is greated than today-365 for instance. I need to have a calculated field "year" on the opp and test on that. 

Or is there something I'm missing? 

hello Damien,

if to use a "sliding window" of 365 days, wouldn't it lead to daily recalculations regardless of the Sugar interface was used or not?
I wonder whether Sugar Logic works well in such cases

Best,
Dmytro

You're absolutely right. This would require a calculated field "is within 365 days" that needs to be refreshed daily. 

Not convienient and resource-intensive. 

ATM my client wants a simpler field as they work on a calendar year-basis. So that field needs to be updated only when the close date is updated. 

In general, Sugar Reports would hardly work for analytical purposes.
However, the Advanced Reports could be of help in some cases.

Absolutely, but this is not usable by business users and AFAIK cannot be displayed in standar dashlets. Just like standard reports are still not contextualized based on the displayed record when shown in a dashlet. 

I know Sugar Reporting is not a BI tool (analytical vs operational), but we're just trying to provide the key information that sales people need to prioritize their tasks. 

Regarding reports contextualized based on the record - there was an extension by AddOptify that makes reports contextual - I tried it with v7 some years ago and it worked great, but not sure whether addon version compatible with v10
https://sugarexchange.sugarcrm.com/apps/7/add-intelligence-reports

Anyway, it won't help with calculations on aggregates or additional filtering with OOTB Reports

For key information, we suggest Sugar Timeline with key events collecting in Sugar and Need ToDo Viewer to focus on priority todos

However, in case the sales team is rather KPI-driven then event-driven, we setup calculation of performance indicators with custom schedulers and make them run daily...
The logic for calculating indicators either coded or configured with Logic Builder if it is possible from a performance perspective -  in some cases, we are forced to use database replicate to maintain indicators calculations to keep main operational CRM instance respond with no delays.

Best,
Dmytro