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]

If I add a "Workdate" Dimension to the aggregation, it returns 0 results.

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

*WorkDate isn't in my chart, but it is at what level the aggregation should be being performed.

and when I try

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

I get results, but if the value is not null, it's the same for each iteration of n, but if it is null,then it is 0.00 for that day.

[Oggy172]

I believe I'm getting the correct results....

[henrikalmen]

Why are there no values? I can only see the expression, not the result of the concat expression.

[Oggy172]

That's what it returns? 

I'm not sure what is the expected result

[marcus_sommer]

Before looking to develop a chart-expression you need to ensure that the existing data-set is suitable. Therefore I repeat my suggestion from above to extend the table-chart to all expression-parts (also the variables) and adding all relevant dimensions (not all at once - else step by step). And further you may also need a table-box with those fields. Are there really all data with their needed associations?

[Oggy172]

Expanded Chart with all Dimensions added, no cartesian joins in the data set

Straight table with the same

Added in variables:

So the dataset looks fine to me?

[marcus_sommer]

I think you will rather find missing or wrong associations instead of cartesian association - at least from the data-model point of view. Within the UI you may have create one by adding the 1 as expression and believe that something like count(WorkingTime) from the facts may more suitable.

Beside this it looked that the min() and max() results have nearly always the same return which doesn't makes sense.

[Oggy172]

They have the same date as it is for today - so the "out" clocking hasn't taken place yet.

Sorry for that confusion

[Oggy172]

and with the count, I am getting normal levels of counts, in that there are <=2 clockings on a given workdate / CDayIndex.

[Oggy172]

Those with >2 registered clocks are genuine and the data is being handled correctly.