topic Re: Multi-Dimension Aggr() in QlikView

Multi-Dimension Aggr()

matthewjbryant — Fri, 22 May 2015 11:07:56 GMT

I'm trying to use the aggr() function with 2 or more dimensions in a chart, but can't get the result I want.

In the attached the totals are correct, but the average [Order Complete] only shows on one detail line per order (when it should show for all). I can't find how to change the aggr() so the detail and the totals are correct.

Any idea?

Re: Multi-Dimension Aggr()

robert_mika — Fri, 22 May 2015 11:15:23 GMT

What is your desired result?

Re: Multi-Dimension Aggr()

sunny_talwar — Fri, 22 May 2015 11:18:09 GMT

Is this what you are looking for?

Try this expression:

=If(Dimensionality() = 0, sum(aggr(avg([Order Complete]),Order)), sum(aggr(NODISTINCT avg([Order Complete]),Order)))

HTH

Best,

Sunny

Re: Multi-Dimension Aggr()

matthewjbryant — Fri, 22 May 2015 11:18:37 GMT

Sorry if that wasn't clear. I want the detail from the table where that is correct (right), but with the totals showing as the table with the correct totals (left).

Re: Multi-Dimension Aggr()

sunny_talwar — Fri, 22 May 2015 11:19:07 GMT

Forgot to add the attachment. PFA your qvw file for reference.

Best,

Sunny

Re: Multi-Dimension Aggr()

matthewjbryant — Fri, 22 May 2015 11:20:39 GMT

Yes!! I've no idea what this function's doing right now, but it's given me some new stuff to read up on!

Thanks

Re: Multi-Dimension Aggr()

sunny_talwar — Fri, 22 May 2015 11:21:18 GMT

Hahahaha it sure does.

Re: Multi-Dimension Aggr()

robert_mika — Fri, 22 May 2015 11:38:40 GMT

Do you mean Dimensionality?

Look here:

How to use - Dimensionality()

Re: Multi-Dimension Aggr()

sunny_talwar — Fri, 22 May 2015 11:48:49 GMT

And you can learn about the concept about NODISTINCT here: What NODISTINCT parameter does in AGGR function?

Best,

Sunny