cancel
Showing results for
Did you mean:
Specialist

## 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!

1 Solution

Accepted Solutions
MVP

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.

6 Replies
MVP

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!

Specialist III
Specialist
Author

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!

MVP

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.

Specialist
Author

You nailed it!

your latter expression did the job!

Thanks!

Partner - Contributor

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

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