topic Re: How to use Aggr() with various conditions in QlikView

How to use Aggr() with various conditions

dafnis14 — Wed, 14 Oct 2015 10:41:10 GMT

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!

Re: How to use Aggr() with various conditions

swuehl — Wed, 14 Oct 2015 12:21:40 GMT

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.

Re: How to use Aggr() with various conditions

gautik92 — Wed, 14 Oct 2015 12:22:44 GMT

Have a look at this

Pitfalls of the Aggr function

Use Aggregation Functions!

Re: How to use Aggr() with various conditions

dafnis14 — Wed, 14 Oct 2015 14:17:09 GMT

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!

Re: How to use Aggr() with various conditions

swuehl — Wed, 14 Oct 2015 14:25:58 GMT

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.

Re: How to use Aggr() with various conditions

dafnis14 — Wed, 14 Oct 2015 14:50:20 GMT

You nailed it!

your latter expression did the job!

Thanks!

Re: How to use Aggr() with various conditions

mlatabhi — Fri, 14 Aug 2020 18:12:20 GMT

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