Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

dafnis14
Contributor III

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
MVP

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.

5 Replies
MVP
MVP

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.

See also

Use Aggregation Functions!

Highlighted
gautik92
Valued Contributor III

Re: How to use Aggr() with various conditions

dafnis14
Contributor III

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!

MVP
MVP

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.

dafnis14
Contributor III

Re: How to use Aggr() with various conditions

You nailed it!

your latter expression did the job!

Thanks!