Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dafnis14
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
swuehl
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
swuehl
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!

gautik92
Specialist III
Specialist III

dafnis14
Specialist
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!

swuehl
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.

dafnis14
Specialist
Specialist
Author

You nailed it!

your latter expression did the job!

Thanks!

mlatabhi
Partner - Contributor
Partner - Contributor

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