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
Did you mean:
Highlighted
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

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

Highlighted
Specialist III
Highlighted
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

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.

Highlighted
Specialist

You nailed it!

your latter expression did the job!

Thanks!

Partner

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

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