Clarify formula with equals Aggr ( = Aggr).

[Morgoz]

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.

[MayilVahanan · Accepted solution]

Hi 

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

[Morgoz]

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!

[anthonyj]

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.

[Morgoz]

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!