
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nested Aggregation not allowed
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)
- « Previous Replies
- Next Replies »


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I would then expect results by an expression like:
sum(aggr(max(S_date_and_time) - min(S_date_and_time),S_employee_id, WorkDate))
Just select at the beginning one or two employees and dates and then extending the scope step by step. If it anywhere doesn't return the wanted results you may reduce the subset again and looking directly on the data in a table-box (missing/wrong values, a day-shift, ...).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Honestly, the data looks absolutely fine. I cannot see any irregularities.
I will try to get a dump and anonymise it tomorrow ... as I have no clue what's going wrong here.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think also that it looked ok. I assume that you want to display the sum against any higher dimensionality as the employee and workdate. Just reduce the dataset per selections to two of them and remove then step by step the more granular dimensions - normally it should work and returning the appropriate sums. Helpful might be also to keep the lower granularities and to add some TOTAL statements to ignore certain parts of the object-dimensionality.
If not you may try to extend the aggr() with a NODISTINCT and to integrate some kind of error/exception-handling, for example to wrap the single and/or the combination of the min/max within an alt() or a rangesum() because each non-numeric parameter of a calculation with normal math. operators will return NULL. Useful might be also the attempt not to calculate the target at once else to return the underlying values which may be done per:
concat(aggr(max(S_date_and_time) & ' - ' & min(S_date_and_time),S_employee_id, WorkDate), ' + ')
By everything be not too fast to discard an unexpected result because it may return valuable hints to the underlying data and why the intended approach may not be working.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I really do appreciate the time and effort ou've put in to help me identify the issue, but I'm afraid I am going to have to admit defeat on this one.
I am a relatively new user to Qlik and I'm struggling more and more to understand what may be wrong, what I'm being asked to try to identify it and what is good/bad results .... where to go next etc.
I have dumped some filtered variants of the tables to file, which if you wish to, you're welcome to have a look. They've been anonymised, but there is no expectation from me for you to do this. It's just more if you wish to settle your own curiosity.
Again, thank you for all your time/help/support, but it's defeated me.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, but it would need too much time to load the xlsx and re-creating a view similar to yours and playing with it + very likely to look afterwards to raw-data to find that they aren't associated properly.
Like above hinted I think that this kind of data-model isn't suitable for your wanted view and quite likely also not for many others, too. Link-table approaches have a lot of disadvantages - even if they build correctly which I doubt for your case. Each table is here associated with an own key to the link-table which will probably ok. just for the common fields between them but such key is not automatically suitable for the parallel-tables.
I wouldn't go in such direction else creating a star-scheme data-model with swipes as fact-table and calculating there all needed information and the employees + calendar are just dimension-tables. The link-table is IMO a detour and superfluous.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I really don't think the data model is at fault here, given I can hard code the values and get the expected result, but as I said, it's defeated me now.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I'm not so sure about it and maybe it's rather the prove that the given data-associations are not supporting your intended views.
Be aware that beside your current challenges there may be far more by creating other "normal" views because like hinted such kind of data-model is complex and even if they are enable all needed views that's more complicated as using the officially recommended star-scheme.

- « Previous Replies
- Next Replies »