Total error in a grid report

[PGI]

Hello,

When I use a grid report for my prospects (I count my prospects created each week), I get a total at the bottom of the report that is wrong. When I export to Excel format, the Excel result is correct and I can see the difference.
Do you need more information to find out where the problem is coming from?

[yuri]

Hi,

Yes please. Advanced Pack version. Report parameters.

[PGI]

Hello,

Here are some details:

EspoCRM Version 5.6.1
Advanced Pack version 2.3.5

And I add the report settings as an attachment.

Thank you.

[yuri]

Still can't reproduce. How much is the difference between correct and shown values?

[PGI]

EspoCRM = 266 (false)
Excel = 238 (true)

I enclose the screenshot of the report. The number per week is correct but at the bottom, the total is not equal to the sum of the weeks.

If I can help you by sending you more information, please do not hesitate.

[yuri]

Could you try to add an additional filter:

your date field -- is not empty.

Thanks

[PGI]

"Is not Empty" does not exist for my date field (see screenshot). Maybe it's a translation problem.

I tried with "Toujours" which is maybe the equivalent of "Is not Empty" but it doesn't fix the problem.

[yuri]

Could you please duplicate the report but with group by month instead of week. Please let me know whether with month it displays correct total. Thanks.

[PGI]

By grouping by month, the results of EspoCRM and Excel are the same.

[yuri]

Could you please try advanced pack 2.3.7.

[PGI]

I tried version 2.3.7. The Excel and EspoCRM results are now consistent... however, I do not understand because a week n°53 appeared for the year 2018 when it does not exist. You can compare the two screenshots posted today to compare.

[yuri]

Try running in MySQL

SELECT YEARWEEK('2018-12-31', 5)

it will return 201853

Week 2018/53 exists and it comprises a 31 which is Monday and a few days from 2019. 2019-01-01 is also 2018/53. It's correct.

[PGI]

The international standard ISO 8601 does not agree with this result. In 2018, it was only 52 weeks ago. December 31, 2018 is included in week number 1 of 2019.

I invite you to read the information in the link to Wikipedia.

Doesn't MySQL solve the problem with SELECT YEARWEEK('2018-12-31', 6) for example?

[yuri]

I will change it in 5.6.2 release. For USA (when Sunday is first day) we won't honor ISO 8601. Only if fdow is set to Monday. Thanks.

[PGI]

Thank you very much!