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]

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

[marcus_sommer]

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.

[Oggy172]

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.

[marcus_sommer]

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

[Oggy172]

Nope,

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

[marcus_sommer]

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?

[Oggy172]

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..

[henrikalmen]

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.

[marcus_sommer]

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.

[henrikalmen]

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)