Nested Aggr() function

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

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.

[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]

Hi Robert,

Try this:

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

[#6]

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)

[Anonymous]

For individual department try below option

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

[#9]

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

[#10]

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!