# Cannot mix aggregate and non-aggregate arguments in reports
**URL:** https://community.fibery.io/t/cannot-mix-aggregate-and-non-aggregate-arguments-in-reports/9753
**Category:** Get Help
**Created:** [October 7, 2025, 3:18pm UTC](https://community.fibery.io/t/cannot-mix-aggregate-and-non-aggregate-arguments-in-reports/9753 "2025-10-07T15:18:13Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@RonMakesSystems](https://community.fibery.io/u/RonMakesSystems)
#### Post date: [October 7, 2025, 3:18pm UTC](https://community.fibery.io/t/cannot-mix-aggregate-and-non-aggregate-arguments-in-reports/9753/1 "2025-10-07T15:18:13Z")
Use case:
See a table with two rows:
1. Created Projects
2. Completed Projects
This is done with a formula field with either “Created” or “Created,Completed”, and a Split function in the report. This is needed because the ones that are completed should also be shown as created. It’s not one or the other.
Next I want a column to show “Last 7 days”. The thing is, depending on the row it should filter for different fields. One is last 7 days based on the Creation Date, and the other on the Done Date. I tried this function:
```auto
IF(
SPLIT(
[Count for Analytics],
','
) == 'Created',
COUNTIF(
[Public Id],
[Creation Date (Real)]> ADD_DAY(
DAY(NOW()),
-7
)
),
COUNTIF( [Public Id],
[Done Date]> ADD_DAY(
DAY(NOW()),
-7
)
)
)
```
Which I think should work, but this error is showing up: Cannot mix aggregate and non-aggregate arguments.
Not sure if there’s a better way to do this, or if I must just do it in two different reports. Open to suggestions!!
Let me know if the need is unclear.
Thanks!
---
### Author:  [@Chr1sG](https://community.fibery.io/u/Chr1sG)
#### Post date: [October 7, 2025, 8:57pm UTC](https://community.fibery.io/t/cannot-mix-aggregate-and-non-aggregate-arguments-in-reports/9753/2 "2025-10-07T20:57:11Z")
If you could write in a single sentence a statement of what you want the report to show, then perhaps it would be easier to get the problem, e.g.
“I want to know how many projects were started in the last 7 days, and how many projects were completed in the last 7 days”
Also, is it essential that the numbers are in tabular form, or could they be KPIs, or maybe a pie chart, or…?
---
### Author:  [@RonMakesSystems](https://community.fibery.io/u/RonMakesSystems)
#### Post date: [October 8, 2025, 6:03am UTC](https://community.fibery.io/t/cannot-mix-aggregate-and-non-aggregate-arguments-in-reports/9753/3 "2025-10-08T06:03:04Z")
I want to know how many projects were created, and how many were completed in the last 7 days, last 30 days, and last 90 days.
I was trying to get this to display as a table so I can see “Created” in one row, and “Completed” in a row below.
The thing is, each column needs to filter differently, per row. So I’m aware this is a bit of hacky solution, but it would provide the cleanest report.
The Created needs to filter by creation date, and Competed needs to filter by completion date.
Right now my solution was to have 2 different reports, but if it could be done in one, that would be ideal.
Let me know if this makes sense!
---
### Author:  [@Chr1sG](https://community.fibery.io/u/Chr1sG)
#### Post date: [October 8, 2025, 6:25am UTC](https://community.fibery.io/t/cannot-mix-aggregate-and-non-aggregate-arguments-in-reports/9753/4 "2025-10-08T06:25:58Z")
> [@RonMakesSystems](#):
>
> I want to know how many projects were created, and how many were completed in the last 7 days, last 30 days, and last 90 days.
I presume you mean “I want to know how many projects were created in the last 7 days, last 30 days, and last 90 days, and how many were completed in the last 7 days, last 30 days, and last 90 days.” right?
> [@RonMakesSystems](#):
>
> I was trying to get this to display as a table so I can see “Created” in one row, and “Completed” in a row below.
With a column each for 7, 30 and 90 days, right?
Does a project created 5 days ago get counted in all 3 columns? Or only the ‘last 7 days’ column?
---
### Author:  [@RonMakesSystems](https://community.fibery.io/u/RonMakesSystems)
#### Post date: [October 8, 2025, 6:29am UTC](https://community.fibery.io/t/cannot-mix-aggregate-and-non-aggregate-arguments-in-reports/9753/5 "2025-10-08T06:29:46Z")
Yes tô all of the above!
> [@Chr1sG](#):
>
> Does a project created 5 days ago get counted in all 3 columns? Or only the ‘last 7 days’ column?
Gets counted in all. I use a COUNTIF creation date is after 7 days ago. Then for monthly, after 30 days ago.
---
### Author:  [@Chr1sG](https://community.fibery.io/u/Chr1sG)
#### Post date: [October 8, 2025, 2:43pm UTC](https://community.fibery.io/t/cannot-mix-aggregate-and-non-aggregate-arguments-in-reports/9753/6 "2025-10-08T14:43:48Z")
Here’s how:
1st column
```auto
SPLIT([Count for Analytics])
```
2nd column (created or completed in last 90 days)
```auto
COUNTIF(
[Public Id],
SPLIT(
[Count for Analytics],
','
) == 'Completed' and DATEDIFF(
[Done Date],
NOW(),
'day'
) < 90
) + COUNTIF(
[Public Id],
SPLIT(
[Count for Analytics],
','
) == 'Created' and DATEDIFF(
[Creation Date (Real)],
NOW(),
'day'
) < 90
)
```
3rd column (created or completed in last 30 days)
_same as above but replace 90 with 30_
4th column (created or completed in last 7 days)
_same as above but replace 90 with 7_
---
### Author:  [@RonMakesSystems](https://community.fibery.io/u/RonMakesSystems)
#### Post date: [October 8, 2025, 2:55pm UTC](https://community.fibery.io/t/cannot-mix-aggregate-and-non-aggregate-arguments-in-reports/9753/7 "2025-10-08T14:55:39Z")
Very nice!!! Okay cool.
So you put the Split inside of the Count, instead of the Count inside of the Split.
Thanks for taking the time for this!
---
### Author:  [@RonMakesSystems](https://community.fibery.io/u/RonMakesSystems)
#### Post date: [October 8, 2025, 4:11pm UTC](https://community.fibery.io/t/cannot-mix-aggregate-and-non-aggregate-arguments-in-reports/9753/8 "2025-10-08T16:11:47Z")
Used AI to extrapolate this to the “Percentage Changed” column and got this formula:
```auto
IF(
(COUNTIF(
[Public Id],
(SPLIT(
[Count for Analytics],
','
) == 'Completed' and DATEDIFF(
[Done Date],
NOW(),
'day'
) >= 90) and DATEDIFF(
[Done Date],
NOW(),
'day'
) < 180
) + COUNTIF(
[Public Id],
(SPLIT(
[Count for Analytics],
','
) == 'Created' and DATEDIFF(
[Creation Date (Real)],
NOW(),
'day'
) >= 90) and DATEDIFF(
[Creation Date (Real)],
NOW(),
'day'
) < 180
)) == 0,
0,
(((COUNTIF(
[Public Id],
SPLIT(
[Count for Analytics],
','
) == 'Completed' and DATEDIFF(
[Done Date],
NOW(),
'day'
) < 90
) + COUNTIF(
[Public Id],
SPLIT(
[Count for Analytics],
','
) == 'Created' and DATEDIFF(
[Creation Date (Real)],
NOW(),
'day'
) < 90
)) - (COUNTIF(
[Public Id],
(SPLIT(
[Count for Analytics],
','
) == 'Completed' and DATEDIFF(
[Done Date],
NOW(),
'day'
) >= 90) and DATEDIFF(
[Done Date],
NOW(),
'day'
) < 180
) + COUNTIF(
[Public Id],
(SPLIT(
[Count for Analytics],
','
) == 'Created' and DATEDIFF(
[Creation Date (Real)],
NOW(),
'day'
) >= 90) and DATEDIFF(
[Creation Date (Real)],
NOW(),
'day'
) < 180
))) / (COUNTIF(
[Public Id],
(SPLIT(
[Count for Analytics],
','
) == 'Completed' and DATEDIFF(
[Done Date],
NOW(),
'day'
) >= 90) and DATEDIFF(
[Done Date],
NOW(),
'day'
) < 180
) + COUNTIF(
[Public Id],
(SPLIT(
[Count for Analytics],
','
) == 'Created' and DATEDIFF(
[Creation Date (Real)],
NOW(),
'day'
) >= 90) and DATEDIFF(
[Creation Date (Real)],
NOW(),
'day'
) < 180
))) * 100
)
```
Looks like its working! Leaving this here if anyone in the future ever needs it :))
---
### Author:  [@RonMakesSystems](https://community.fibery.io/u/RonMakesSystems)
#### Post date: [October 8, 2025, 4:27pm UTC](https://community.fibery.io/t/cannot-mix-aggregate-and-non-aggregate-arguments-in-reports/9753/9 "2025-10-08T16:27:41Z")
Maybe your genius could help with the last part of this:
I have these grouped by client, so on each client it shows the number created this week and the number completed this week.
Now at the bottom, the totals are not split by completed/created so it totals everything. Is there a way for the total’s row to be split as well, or is the functionality not there yet?
Thank you so much!
Edit:
NEVERMIND! I got it, it’s a bit of a hack but it works.
I added a field in the project db that’s just “Client.Name + “,Total”” then I group by Split([Client for Analytics],“,”)
Then it adds a new “Client”-ish called “Total” and then it works!
Edit2:
Changed “z Total” so that it becomes the last group.