Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! __REGISTER TODAY__ and save!

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Clarify formula with equals Aggr ( = Aggr).

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Morgoz

Contributor III

2021-07-26
05:34 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Clarify formula with equals Aggr ( = Aggr).

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.

885 Views

1 Solution

Accepted Solutions

MayilVahanan

MVP

2021-07-27
12:23 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Thanks & Regards, Mayil Vahanan R

Please close the thread by marking correct answer & give likes if you like the post.

Please close the thread by marking correct answer & give likes if you like the post.

4 Replies

MayilVahanan

MVP

2021-07-27
12:23 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Thanks & Regards, Mayil Vahanan R

Please close the thread by marking correct answer & give likes if you like the post.

Please close the thread by marking correct answer & give likes if you like the post.

Morgoz

Contributor III

2021-07-27
05:46 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

813 Views

anthonyj

Creator III

2021-07-27
09:14 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

797 Views

Morgoz

Contributor III

2021-07-28
02:15 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

Community Browser