Nested Aggregation not allowed

[Oggy172]

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) 

[Oggy172]

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)

[henrikalmen]

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

[Oggy172]

No dice I'm afraid.

10/03/2025 selected

-6 days 

and -2 Days, provide the same data

[henrikalmen]

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.

[Oggy172]

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

[Oggy172]

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

[henrikalmen]

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? 

[Oggy172]

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.

[marcus_sommer]

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)