topic Re: Clarify formula with equals Aggr ( = Aggr). in App Development

Clarify formula with equals Aggr ( = Aggr).

Morgoz — Mon, 26 Jul 2021 21:34:40 GMT

Hello,

could someone please teach me what does this measure formula does?

-Sum( Base = Aggr(NoDistinct Max(Base), Contra))

The part that is getting me crazy is the "Base = Aggr". Up what I know the Aggr function returns a table, in the example above a table with all the maximum values of "Base" in "Contra", but if I remove the "Sum" and leave the measure like

Base = Aggr(NoDistinct Max(Base), Contra)

It shows minus the number of maximum values that are equal per "Contra" dimension...

For more information, it comes from a solved post of mine: post

Need clarification please!!

Thanks.

Re: Clarify formula with equals Aggr ( = Aggr).

MayilVahanan — Tue, 27 Jul 2021 04:23:26 GMT

Its short form of below

Sum( If(Base = Aggr(NoDistinct Max(Base), Contra), 1, 0))

Ur dimension is Contra

So, its check the base = max(base) for that contra, if its yes, gives 1 else 0.

---------------

Base = Aggr(NoDistinct Max(Base), Contra)

It shows minus the number of maximum values that are equal per "Contra" dimension...

Yes, its gives minus, that the reason, "minus" is added before sum() & gives the correct result

-Sum( Base = Aggr(NoDistinct Max(Base), Contra))

Re: Clarify formula with equals Aggr ( = Aggr).

Morgoz — Tue, 27 Jul 2021 21:46:11 GMT

Thank you Mayil,

now just tell me please, why does it returns "minus"? From your response, the formula is an abreviation of

Sum( If(Base = Aggr(NoDistinct Max(Base), Contra), 1, 0))

And I don't see any minus in that formula... Base is always a positive number. Wouldn't it be

Sum( If(Base = Aggr(NoDistinct Max(Base), Contra), -1, 0)) ?

But in any case why with minus?

Thank you!

Re: Clarify formula with equals Aggr ( = Aggr).

anthonyj — Wed, 28 Jul 2021 01:14:37 GMT

Hi,

The calculation is implicitly summing the TRUE and FALSE results from the "Base = Max(Base)" calculation. Qlik calculates TRUE as -1 and FALSE as 0. This is why you end up with a negative number. To get around this you can wrap your calculation in the ABS( ) function or just multiply by -1.

Re: Clarify formula with equals Aggr ( = Aggr).

Morgoz — Wed, 28 Jul 2021 06:15:43 GMT

Great!

Thank you Anthony, now everything is clear.

By your response I think that the equivalent formula would be

Sum( If(Base = Aggr(NoDistinct Max(Base), Contra), -1, 0))

But in any case I'll accpet Mayil's answer as correct.

Thank you again to both!