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!

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!

Community Browser