Cannot mix aggregate and non-aggregate arguments in reports

Get Help
1

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:

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!

2

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…?

3

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!

4

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?

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?

5

Yes tô all of the above!

Gets counted in all. I use a COUNTIF creation date is after 7 days ago. Then for monthly, after 30 days ago.

6

Here’s how:

1st column

SPLIT([Count for Analytics])

2nd column (created or completed in last 90 days)

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

7

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!

8

Used AI to extrapolate this to the “Percentage Changed” column and got this formula:

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 :))

9

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.