Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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

View solution in original post

11 Replies
Gysbert_Wassenaar

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
marcus_sommer

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

sunny_talwar

May be this:

Sum(Aggr(UnitCost * (Sum(UnitCount)/Count(DeviceID)), Department, DeviceID))

Anonymous
Not applicable
Author

Hi Robert,

Try this:

=sum( aggr(sum(UnitCost)*( sum(TOTAL <Department> UnitCount) / count(TOTAL <Department> DISTINCT DeviceID)), DeviceID, Department))

Not applicable
Author

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

Gysbert_Wassenaar

Try this: count({<DeviceType={"TypeA"}>} DISTINCT TOTAL <Department> DeviceID)


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

For individual department try below option

=sum({<Department={'Dept1'}>} UnitCost) * (sum( {<Department={'Dept1'}>} UnitCount) / count( {<Department={'Dept1'}>} DISTINCT DeviceID))

Not applicable
Author

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

Not applicable
Author

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!