Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
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
Highlighted
MVP
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.

View solution in original post

6 Replies
Highlighted
MVP
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.

See also

Use Aggregation Functions!

Highlighted
Specialist III
Specialist III

Specialist
Specialist

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!

Highlighted
MVP
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.

View solution in original post

Highlighted
Specialist
Specialist

You nailed it!

your latter expression did the job!

Thanks!

Highlighted
Partner
Partner

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