Understanding Aggr function

[kamal_sanguri]

Hi,

I was just exploring Old Community threads and found this one Limit Dimensions by Calculated Expression

The solution in this thread is working fine however I am not able to make much sense out of it. The way I am trying to understand the below expression is- IF function would be evaluated first. So it will filter out all the items not fulfilling this condition and then the outer function Aggr would be evaluated with the filtered data.

=aggr(if(SUM(LINE_SPEND_AMOUNT)>50000, [Supplier Name]),[Supplier Name]

However, this function seems to be working in a different way aggr is getting evaluated first.. so, it is first evaluating aggr to group LINE_SPEND_AMOUNT by Supplier Name and then If function plays it's role by filtering out data not fulfilling the condition.

Attaching my QV doc to better understand this.

[tresesco]

Let's talk with reference to your sample attached. What is your expected output in the text box? May be we can start discussing the logic of aggr()/if evaluation from there.

[kamal_sanguri]

The expression in text box shows the Sales Person who has a combined sales of less than 8 million ( I am using the same expression provided in referenced thread) and it is working fine.

So, that means aggr is getting evaluated first and then if condition checks that John is the one who has combined sales less than 8 million.

=aggr(if(SUM(Sales)<8000000, SalesMan),SalesMan)

[tresesco]

Yes.

[kamal_sanguri]

Ok. So, is it the correct order because what I think is inner function should be calculated first and the output of the inner function should become the input for the outer function.

Also, If aggr is evaluated first how does it know that we want to use sum(sales) as aggregation as it is still inside if which is still not calculated.

OR If you can explain how this calculation is happening.

Sorry, Not trying to create confusion.. I am confused though.

Thanks

[tresesco]

Aggr() doesn't perform anything except for creating a virtual table for available records. So, in your case, aggr creates table virtually which has SalesMan as a dimension. If(Sum... starts working from there. Hope this helps.

[kamal_sanguri]

Hello stalwar1‌, swuehl‌, gwassenaar

I know you would be able to make me understand this (you have done it in past as well )

Thanks in advance!!

-Kamal

[kamal_sanguri]

Thanks  Tresesco, Yeah It helped. But still not able to convince myself.

[tresesco]

Well, let me put your question to yourself.

If IF has to be evaluated first, you are asking IF to show SalesMan where summed sales if greater than some value. Now IF would need information against which Dimension this aggregation should happen, you come back to aggr() for that information and get dimension SalesMan. Now actually the aggregation can start and then only IF can get evaluated and show you the SalesMan, right? So what became the actual order :

Aggr->IF->Sum->IF

One more point, remember, if multiple SalesMan value qualify the condition, you would see nothing in the text box.

??

[kamal_sanguri]

OK, So firstly AGGR derived a virtual table like this

then, IF started doing calculations based on the result it gets from AGGR [sum(sales)] and it is the IF function which eventually threw out the output not the AGGR.

I think I am getting it now (if you think the same way)

Regards,

-Kamal