topic Re: Nested Aggr() function in QlikView

Nested Aggr() function

Thu, 08 Dec 2016 12:36:15 GMT

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

Re: Nested Aggr() function

Gysbert_Wassenaar — Thu, 08 Dec 2016 12:41:51 GMT

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.

Re: Nested Aggr() function

marcus_sommer — Thu, 08 Dec 2016 12:43:57 GMT

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

Re: Nested Aggr() function

sunny_talwar — Thu, 08 Dec 2016 12:45:00 GMT

May be this:

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

Re: Nested Aggr() function

Anonymous — Thu, 08 Dec 2016 13:41:06 GMT

Hi Robert,

Try this:

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

Re: Nested Aggr() function

Thu, 08 Dec 2016 14:30:36 GMT

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

Re: Nested Aggr() function

Gysbert_Wassenaar — Thu, 08 Dec 2016 14:33:00 GMT

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

Re: Nested Aggr() function

Anonymous — Thu, 08 Dec 2016 14:43:54 GMT

For individual department try below option

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

Re: Nested Aggr() function

Thu, 08 Dec 2016 14:54:43 GMT

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

Re: Nested Aggr() function

Thu, 08 Dec 2016 15:03:52 GMT

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!

Re: Nested Aggr() function

Thu, 08 Dec 2016 15:06:05 GMT

Thanks for your response Marcus!

You're right, I was missing an aggregation!! D'oh!

Your solution wasn't yielding quite the results I expected though I'm not sure why; it looks good! Thanks for your response!

Re: Nested Aggr() function

Thu, 08 Dec 2016 15:13:29 GMT

Thanks for your reply, Sunny! I went with Gysbert's answer as I think it covered the requirement for looking at the TOTAL devices in a department, however I think your solution would have worked if I modified it to my requirements a bit.

Appreciate you answering my question - Thanks!