topic Re: Nested Aggregation not allowed in App Development

Nested Aggregation not allowed

Oggy172 — Thu, 13 Mar 2025 13:08:13 GMT

Hi folks,

Trying to write some set analysis, but getting this error if someone can advise how to fix & why it's going wrong.

sum({$<[Date Index] =, [Date Index] = {"$(=$(vDateIndex))"}>}((max(S_date_and_time) - min(S_date_and_time)) * 24))

vDateIndex is a variable set when a user makes a selection, and I need to return the result of

((max(S_date_and_time) - min(S_date_and_time)) * 24)

For the date selected (minus 2 days)

Re: Nested Aggregation not allowed

Oggy172 — Thu, 13 Mar 2025 13:59:01 GMT

I've progressed it, but I'm not getting the expected results.

If I select 10/03/2025 (vDateIndex = 1353) - I want to see the data from 08/03/2025 (vDateIndex = 1351), but it is still showing me 10/03/2025.

sum({1<[CDayIndex] =, [CDayIndex] = {$(=$(vDateIndex)-2)}>} aggr(((max(S_date_and_time) - min(S_date_and_time)) * 24),S_employee_id))

Though the expression editor shows it to be working ok?!?

Returning 07:57

Which is the data for 10/03/2025 (1353)

Re: Nested Aggregation not allowed

henrikalmen — Thu, 13 Mar 2025 17:14:30 GMT

sum({1<[[CDayIndex] = {">=$(=$(vDateIndex)-2)"}>} aggr(((max(S_date_and_time) - min(S_date_and_time)) * 24),S_employee_id))

Re: Nested Aggregation not allowed

Oggy172 — Fri, 14 Mar 2025 11:43:57 GMT

No dice I'm afraid.

10/03/2025 selected

-6 days

and -2 Days, provide the same data

Re: Nested Aggregation not allowed

henrikalmen — Fri, 14 Mar 2025 12:19:42 GMT

Did you possibly forget a character? [CDayIndex]={">=$(=$(vDateIndex)-2)"}>}

You may also need to set CDisplayDate= within the set expression, so that the selected value in that field doesn't apply.

Re: Nested Aggregation not allowed

Oggy172 — Fri, 14 Mar 2025 12:22:28 GMT

I removed that as I don't want greater than or equal to? Only equal to?

Re: Nested Aggregation not allowed

Oggy172 — Fri, 14 Mar 2025 12:26:59 GMT

Edited to

sum({$<CDisplayDate=, [CFiscal Week]=, [CDayIndex] = {">=$(=$(vDateIndex)-5)"}>} aggr(((max(S_date_and_time) - min(S_date_and_time)) * 24),S_employee_id,WorkDate))

sum({1<CDisplayDate=, [CFiscal Week]=, [CDayIndex] = {">=$(=$(vDateIndex)-5)"}>} aggr(((max(S_date_and_time) - min(S_date_and_time)) * 24),S_employee_id,WorkDate))

sum({$<CDisplayDate=, [CFiscal Week]=, [CDayIndex] = {">=$(=$(vDateIndex)-2)"}>} aggr(((max(S_date_and_time) - min(S_date_and_time)) * 24),S_employee_id,WorkDate))

sum({1<CDisplayDate=, [CFiscal Week]=, [CDayIndex] = {">=$(=$(vDateIndex)-2)"}>} aggr(((max(S_date_and_time) - min(S_date_and_time)) * 24),S_employee_id,WorkDate))

and I get the same results every time

Re: Nested Aggregation not allowed

henrikalmen — Fri, 14 Mar 2025 12:40:05 GMT

Maybe I misunderstand what it is you want, I thought you needed to sum values for all dates greater than or equal to the date that was a few days ago.

Maybe you should try something simpler first, in a straight table instead of a pivot table, to see when calculations work and when they start to go wrong. And perhaps try something without the aggr wrapping. Also, I don't know what S_date_and_time contains, if those dates are linked to the other periods (related to vDateIndex) that could be where this goes wrong. And you have WorkDate in the aggr function, how is that date field related to everything else?

Re: Nested Aggregation not allowed

Oggy172 — Fri, 14 Mar 2025 12:58:29 GMT

Yes, you've misunderstood.

I need to review hours worked for 6 days previous to a selected date (CDisplayDate)

CDisplayDate, when set, will trigger a variable to be set:

I then need to look back 6 days from the selected CDisplayDate and return the hours worked.

For example, I built this based on Today() and not a Selected Date, so it will always look back 6 days from today.

The reference to WorkDate is just from a copy / paste of an existing chart where WorkDate is a dimension.

The one for Today() uses teh aggr and it worked fine, so i expected it to also work when I'm only changing the CDayIndex part.

sum(if([CDayIndex] = $(vTodayIndex) - 6, aggr(((max(S_date_and_time) - min(S_date_and_time)) * 24),S_employee_id) , null()))

S_date_and_time are the clocking records (DD/MM/YYYY hh:mm:00)

[Swipes.Day Index] is left joined from [ClockingsCalendar.CDayIndex] on WorkDate

The Data model is as below, everything on the right is linked to the Employee's table, and not used (Employee is the last table used, for their names)

Everythig in the Pivot is on the left side.

Re: Nested Aggregation not allowed

marcus_sommer — Fri, 14 Mar 2025 13:09:02 GMT

Each aggregation-part may need an appropriate defined selection state which may look (simplified) like:

sum({ Set } aggr({ Set } max({ Set } - min({ Set }), Dim1, Dim2))

Beside the above you may to consider to transfer some of the logic into the data-model, for example by flagging the first and/or last record respectively all records and/or numbering them and/or adding a accumulation and then the expression may simply look like:

sum({ < LastAccumulatedRecord = {1}>} WorkingTime)

Re: Nested Aggregation not allowed

Oggy172 — Fri, 14 Mar 2025 13:25:55 GMT

Assuming I've understood you correctly, this still returns the same data:

sum({$<CDisplayDate=, [CFiscal Week]=, [CDayIndex] = {"=$(=$(vDateIndex)-2)"}>} aggr({$<CDisplayDate=, [CFiscal Week]=, [CDayIndex] = {"=$(=$(vDateIndex)-2)"}>} ((max({$<CDisplayDate=, [CFiscal Week]=, [CDayIndex] = {"=$(=$(vDateIndex)-2)"}>}S_date_and_time) - min({$<CDisplayDate=, [CFiscal Week]=, [CDayIndex] = {"=$(=$(vDateIndex)-2)"}>}S_date_and_time)) * 24),S_employee_id))

Re: Nested Aggregation not allowed

marcus_sommer — Fri, 14 Mar 2025 14:15:57 GMT

By looking on your data-model I have serious doubts that's suitable for the wanted views. Your statement that the shown left and right side are irrelevant to each other is quite likely not the case and each part will impact the others. Further noticeable is that many tables are associated against a link-table which is a quite complex approach because it's not trivial to create all key-fields properly by ensuring that all needed key-values exists.

Therefore I suggest to go a few steps back and removing the entire set analysis stuff to see if the main-calculation is working against various selections in the filter-panes. In this regard it may also be useful to add the min/max/sum as plain-aggregations in parallel and/or adding the dayindex and the employee as further dimensions. Each single one must be working for themselves - and you might discover that the single aggr-dimension may not enough else it might also any period-dimension. If yes, they could be combined again + getting more complexity with the conditions - step by step. If not, you should apply all relevant fields + a definitely unique key within a table-box, to see all existing data with their associations to each other.

Like already hinted above I think you should move (much) more logic into the data-model to simplify the UI.

Re: Nested Aggregation not allowed

Oggy172 — Fri, 14 Mar 2025 15:05:06 GMT

I get the exact same behavior with the right tables dropped, so I don't think they are influencing it. We can agree to disagree on that point.

They are now dropped while I work on this, so they won't be impacting it in any way.

If I hardcode the DayIndex, it works fine

{1352}

{1353}

So it's got to be a combination of using aggr() with Set Analysis.

Re: Nested Aggregation not allowed

marcus_sommer — Mon, 17 Mar 2025 07:39:11 GMT

If you replace the variable with a fixed value and getting the wanted results it means that the variable creation/call isn't suitable - assuming that the variable should have a return of 1253 you may apply:

{$(=$(vDateIndex)-2)}

Re: Nested Aggregation not allowed

Oggy172 — Mon, 17 Mar 2025 12:20:27 GMT

Nope,

I still get the same value for every day (Except -4, but I suspect hours worked for that day are null)

Re: Nested Aggregation not allowed

marcus_sommer — Mon, 17 Mar 2025 12:31:32 GMT

Are this n expressions and each one has the above variable-call unless the -n is different or is it a single expression against n dimension-values?

Re: Nested Aggregation not allowed

Oggy172 — Mon, 17 Mar 2025 12:36:32 GMT

It's just the same expression with a different offset

sum({1<[CDayIndex] = {$(=$(vDateIndex)-6)}>} aggr(((max(S_date_and_time) - min(S_date_and_time)) * 24),S_employee_id))

sum({1<[CDayIndex] = {$(=$(vDateIndex)-5)}>} aggr(((max(S_date_and_time) - min(S_date_and_time)) * 24),S_employee_id))

sum({1<[CDayIndex] = {$(=$(vDateIndex)-4)}>} aggr(((max(S_date_and_time) - min(S_date_and_time)) * 24),S_employee_id))

etc..

Re: Nested Aggregation not allowed

henrikalmen — Mon, 17 Mar 2025 12:40:18 GMT

Possibly, your issue is this: "Set Analysis can't vary by chart row, because the set is computed only once, before the chart is calculated." I'm quoting @rwunderlich who wrote that here.

Re: Nested Aggregation not allowed

marcus_sommer — Mon, 17 Mar 2025 12:57:55 GMT

Ok. it are n different expressions - this part should be working. But especially the dimensions from the aggr() doesn't look suitable - only an employee and no period-information. This means the max-min is only performed against the employee with no other consideration of dimensions or conditions - unless the selection state.

Re: Nested Aggregation not allowed

henrikalmen — Mon, 17 Mar 2025 13:03:24 GMT

Maybe you could try completely different expressions just to test the value of the set expression result.

concat({<[CDayIndex] = {$(=$(vDateIndex)-6)}>}distinct CDayIndex)
concat({<[CDayIndex] = {$(=$(vDateIndex)-5)}>}distinct CDayIndex)
concat({<[CDayIndex] = {$(=$(vDateIndex)-4)}>}distinct CDayIndex)