Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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

Tags (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Nested Aggr() function

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
11 Replies
MVP & Luminary
MVP & Luminary

Re: Nested Aggr() function

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
MVP & Luminary
MVP & Luminary

Re: Nested Aggr() function

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

May be this:

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

payalgosar
Contributor II

Re: Nested Aggr() function

Hi Robert,

Try this:

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

Not applicable

Re: Nested Aggr() function

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

MVP & Luminary
MVP & Luminary

Re: Nested Aggr() function

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


talk is cheap, supply exceeds demand
payalgosar
Contributor II

Re: Nested Aggr() function

For individual department try below option

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

Not applicable

Re: Nested Aggr() function

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

Re: Nested Aggr() function

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!