Multi-Dimension Aggr()

[matthewjbryant]

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?

[robert_mika]

What is your desired result?

[sunny_talwar · Accepted solution]

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

[matthewjbryant]

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

[sunny_talwar]

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

Best,

Sunny

[matthewjbryant]

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

[sunny_talwar]

Hahahaha it sure does.

[robert_mika]

Do you mean Dimensionality?

Look here:

How to use - Dimensionality()

[sunny_talwar]

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

Best,

Sunny