Analytics (Pro 2.11.0): several numbers count something other than their label says

Hi Shahjahan Jewel and FluentCommunity team,

we rely on the analytics pages to steer our community, so we checked every
number against the database (FluentCommunity and Pro 2.11.0, MySQL without
ONLY_FULL_GROUP_BY, site timezone Europe/Berlin). Most widgets count rows
created in the selected range, even where the label promises a total. Below
are the cases, each with the method responsible and a suggested fix.

1. Members → "Total Members" runs the same query as "New Members"

Members::getMemberWidget() builds $totalMembers and $newMembers with the
identical call (XProfile, created_at in range). On our site both show 11;
the community has 230 active profiles.

Fix: count XProfile with status = 'active' and no date filter (or
created_at <= end_date for a snapshot).

2. Activity charts always drop the last day

ReportGenerator::getModelDataBySequence() filters with
whereBetween($field, [from->format('Y-m-d'), to->format('Y-m-d')]). The end
is therefore midnight at the start of the last day. With the default range
ending today, today is never shown: for 30 days our comments chart sums to 369
while the widget above it says 388.

Fix: use to->format('Y-m-d') . ' 23:59:59' and include the end date in
the DatePeriod.

3. Ranges of 63–92 days: weekly buckets don't line up with the axis

The query groups by WEEK(created_at) but selects DATE(created_at), which
(without ONLY_FULL_GROUP_BY) is an arbitrary day of that week, while the
axis steps in 7-day intervals from the start date. For 2026-06-01 to
2026-08-20 the chart shows 22 points instead of 12, every other one zero.

Fix: select and group by a bucket start, e.g.
DATE_SUB(DATE(created_at), INTERVAL WEEKDAY(created_at) DAY), and build the
axis from the same rule.

4. Ranges over 92 days: months from different years are merged

The query groups by MONTH(created_at) without the year. For 2025-06-01 to
2026-09-25, "Jun 2025" shows 1,013 comments (618 from June 2025 plus 395 from
June 2026), and June to September 2026 show 0.

Fix: group by DATE_FORMAT(created_at, '%Y-%m').

5. Spaces → "Popular Spaces" mixes all-time comments into a range ranking

Spaces::getTopSpaces() filters posts and members by the range, but
comments_count is the sum over all published posts of the space. Since
the ranking adds the three, all-time comments dominate: spaces without a
single post, comment or join in the range rank in our top 10 (one space shows
3,484 comments; 177 of them fall into the range).

Fix: count comments with created_at in range, as for posts.

6. Smaller points

• "Top Members" (Members::getTopMembers()) only includes members who
  joined in the range, sorted by all-time points. It shows the newest
  members, not the top ones.
• "Active Members" counts profiles whose last_activity is in the range.
  Since that column holds only the latest visit, the number is right for a
  range ending today and wrong for any past range; the comparison value
  therefore compares against members who have not been back since.
• Comparison percentage compares with the same range shifted by one
  calendar month, not with the preceding period of equal length. For 7 days
  that is a week a month ago; for 90 days the periods overlap.
• Posts count every status of type text, including scheduled and
  unlisted (77 instead of 71 published for us).
• Spaces → "Total Members" counts new profiles (optionally filtered by
  current membership), not space joins (11 vs. 153 joins).
• Default dates use gmdate(), so between midnight and 2 a.m. (CEST) the
  default range starts a day early.

Happy to test a fix on our staging site.