5 Replies Latest reply: Oct 14, 2015 10:50 AM by Dafnis X

# How to use Aggr()  with various conditions

Hi,

I would like to have a straight table with one dimension:  Country, which shows various totals of the country's branches.

Some of the calculations involve checking conditions based on branch definitions.

But I'm not sure if I can do it by using the Aggr() function. From what I tried so far, nothing came out..

A tweaked example:  all the fields marked in blue are defined for a branch:

Calculating total number of employees, which is based on some conditions:

if(SomeFlag=1 and isnull(SomeDate)=0,
if(StockFlag=1 and SmallBranch = 0,
sum({<YearSale=,MonthNameSale =>} aggr(max(BranchNoOFEmployees), BranchName)*vNumOfXXX)),

sum ({<YearSale =,MonthNameSale =>} aggr(max(BranchNoOFEmployees), BranchName)*vNumOfYYY)))

There might be some syntax errors, but i'm more concerned about the logic to be applied.

Thanks!

• ###### Re: How to use Aggr()  with various conditions

Your dimension is Country and a specific country shows multiple branches, right?

When you use above as expression, QV will try to evaluated the if() statement conditions in the context of the dimension line, i.e. a specific branch country. It is only able to do so if SomeFlag,SomeDate, StockFlag and SmallBranch is unambiguous in that context.

Use Aggregation Functions!

• ###### Re: How to use Aggr()  with various conditions

Makes sense..

What is the recommended way to calculate such a metric?

It is used also as a part of another expression in the table.

Thanks!

• ###### Re: How to use Aggr()  with various conditions

I am not really sure what you are trying to achieve. Some more details would definitely help.

In general, you can add a second dimension, Branch, to split your metric across Countries and Branches.

Or leave it with one dimension, and create several expressions, limiting your expressions each to a single branch.

You can use Set Analysis to do this:

=Sum({<BranchName = {'A'}>} Value)

Looking at your above code, maybe all you need to do is to embed the conditional in your aggregation (similar to what Henric discussed in his blog post):

sum({<YearSale=,MonthNameSale =>}

aggr(

if(SomeFlag=1 and isnull(SomeDate)=0,
if(StockFlag=1 and SmallBranch = 0,
vNumOfXXX, vNumOfYYY)) *

max(BranchNoOFEmployees)

, BranchName)

)

But latter is hard to decide without knowing anything about your setting.

• ###### Re: How to use Aggr()  with various conditions

You nailed it!

your latter expression did the job!

Thanks!