How to use Aggr() with various conditions

[dafnis14]

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!

[swuehl]

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.

See also

Use Aggregation Functions!

[gautik92]

Have a look at this

Pitfalls of the Aggr function

Use Aggregation Functions!

[dafnis14]

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!

[swuehl · Accepted solution]

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.

[dafnis14]

You nailed it!

your latter expression did the job!

Thanks!

[mlatabhi]

i tried this it did not work i currently have this ranking 

=if(aggr(rank(Sum(amt1)/Sum(amt2)),Dimension1)<=10 , Dimension1)

I need to add condition 

if ( dimension2 = 1 and dimension3= 2, dimension1)

how do i incorporate the if condition i tried various ways no luck thanks