
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nested Aggr() function
Hi all,
Hoping I could get some input on this - I've been stumped though I feel it should be fairly straightforward.
I have an expression currently looking like this (though evidently quite non-functional!)
sum(
Aggr(
UnitCost * aggr(sum(UnitCount) / count(DISTINCT DeviceID) ,[Department]), DeviceID)
)
If it were related to a single device (DeviceID) it would be as follows:
UnitCost * (Department Unit Count / Department Device Count)
So I want the above expression to be carried out per device; each of their unique UnitCost values multiplied by unit department's average units per device. This all has to occur within a text object to provide a single total.
The devices are added in the dashboard as a dynamic update, rather than as part of the reload. If the device count could be derived during the load script, I'd simply add a value against each department of their "Average Units Per Device" and it would be a little more straightforward.
I feel I'm missing something obvious; if anyone can provide a little input that would be great.
Thanks in advance!!
- Tags:
- aggr (expr)
- aggr help
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Perhaps like this: sum( aggr( sum( UnitCost ) * sum(TOTAL <Department> UnitCount) / count(DISTINCT TOTAL <Department> DeviceID), UnitIdentifierHere ) )
If not, please post a small Qlikview document with example data that demonstrates the problem.
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Perhaps like this: sum( aggr( sum( UnitCost ) * sum(TOTAL <Department> UnitCount) / count(DISTINCT TOTAL <Department> DeviceID), UnitIdentifierHere ) )
If not, please post a small Qlikview document with example data that demonstrates the problem.
talk is cheap, supply exceeds demand


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You are nesting aggr() without an aggregation and this shouldn't work (properly). I think you could try something like this:
sum(
Aggr(
UnitCost * avg(aggr(sum(UnitCount) / count(DISTINCT DeviceID) ,[Department])), DeviceID)
)
- Marcus

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this:
Sum(Aggr(UnitCost * (Sum(UnitCount)/Count(DeviceID)), Department, DeviceID))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Robert,
Try this:
=sum( aggr(sum(UnitCost)*( sum(TOTAL <Department> UnitCount) / count(TOTAL <Department> DISTINCT DeviceID)), DeviceID, Department))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Gysbert,
I think that's on the right track - as well as Payal's response below. I'm not getting the exact numbers I expect as I need to select a subset of a particular departments devices..
The use of <Department> is new to me; could you possibly explain just what that is doing? Also, can I add additional set modifers to it? I didn't include the set analysis in my original request because I didn't want to overcomplicate where unnecessary, but I'd need it something along the lines of:
count(DISTINCT TOTAL <Department, DeviceType={"TypeA"}> DeviceID)
so I assume I'd maybe remove the TOTAL keyword also?
Thanks!!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this: count({<DeviceType={"TypeA"}>} DISTINCT TOTAL <Department> DeviceID)
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For individual department try below option
=sum({<Department={'Dept1'}>} UnitCost) * (sum( {<Department={'Dept1'}>} UnitCount) / count( {<Department={'Dept1'}>} DISTINCT DeviceID))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Gysbert, that's spot on.
I'm getting exactly the results I'm expecting across multiple departments, devices (and their specific unit costs).
I'll continue to test, but I'm confident that's it working exactly as intended.
Thanks so much for your help.
For the sake of future visitors, this is the expression I have ended up with:
sum( aggr(sum({<[DeviceType]=>}UnitCost)*( sum(TOTAL <[Department]> Units) / count({<[DeviceType]={"TypeA"}>} DISTINCT TOTAL <[Department]> DeviceID)), DeviceID))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, Payal, for this response and your previous response below! I used Gysbert's solution, however with a little testing, it looks like yours worked too and with the same modifications above would likely have worked equally well as a solution. Thank you!

- « Previous Replies
-
- 1
- 2
- Next Replies »