# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

## Aggr fuction with If Conditoins

Hi,

I have the following formula, that gives me the number of KN_ID´s under the If-Conditions. I Need the "Distinct" keyword. because otherwise there would be duplictates.

Count(Distinct If(
(K1 > vLimit_K1    and  Date = vDate and (

K2 >  vLimit_K2    or     K3 < vLimit_K3

)

)

KN_ID,0))-1

Now I want to Sum a field (KN_IA), which is linear (every KN_ID has exactly one KN_IA) to KN_ID but just for the KN_ID´s, which I get with the IF-Expression.

I tried to do this with Set Analysis, but I am done with that. For some reasons it is not possible in my case.

Therefore I thought of doing that using Aggr-function. The Expression should look somehting like this:

Sum(Aggr(

Sum(KNE_IA),

If(   K1 > vLimit_K1    and  Date = vDate and (  K2 >  vLimit_K2    or     3 < vLimit_K3 )  ),  KN_ID)

))

To sum it up: I want to create a temporary table using aggr function. In that table should be only the KN_ID´s which meet the if-conditions and the belonging KN_IA values. And in this Table I want to sum the KN_IA.

In the Load Script it would be easy to create a new field, which just consists of the needed values for KN_ID, but I use variables and therefore that will not be possible.

I am looking forward to a response. Thank you in advance.

Best regards

thorqlik

1 Solution

Accepted Solutions
MVP

## Re: Aggr fuction with If Conditoins

Can you try this:

Sum(Aggr(Sum(DISTINCT If(K1 > vLimit_K1 and Date = vDate and (K2 > vLimit_K2  or 3 < vLimit_K3)), KNE_IA), KN_ID))

12 Replies
Contributor II

## Re: Aggr fuction with If Conditoins

Hi Thorben

Is all of your data in one table?

What fields do you expect as dimension and measure?

/Teis

MVP & Luminary

## Re: Aggr fuction with If Conditoins

Sum(If( (K1 > vLimit_K1    and  Date = vDate and (K2 >  vLimit_K2    or     K3 < vLimit_K3 ) KN_IA,0))

or perhaps this:

sum({<K1={">\$(vLimit_K1)"},Date={\$(vDate)}, KN_ID={"=K2>\$(vLimit_K2) or K3<\$(vLimit_K3)"} >}KN_IA)

talk is cheap, supply exceeds demand
Not applicable

## Re: Aggr fuction with If Conditoins

the data is in many different tables. The Dimension should be the KN_ID´s, which meet the if-conditions and the measure should be the Amount of KN_IA. As every KN_ID has exactly one KN_IA (if the table is distinct) the Aggregation for the measure should be sum.

Not applicable

## Re: Aggr fuction with If Conditoins

If I use this Expression I sum a lot of duplicates (due to the data modell). Therefore I needed the "Distinct" keyword for the count-Aggregation.

But If I used "Distinct" here, some values that should be summed would not be summed. Because the KN_ID´s are all different, but the KN_IA of two different KN_ID´s can be the same.

Contributor II

## Re: Aggr fuction with If Conditoins

Hi Thorben

If the relation is one to one then if maybe possible to map KN_IA on KN_ID. Then apply the filter in your expression in a Where statement in script.

Ex. as a presiding load or as a new table after mapping table

DataTable2:

KN_ID,

KN_IA

Resident DataTable1

Where

K1 > vLimit_K1

and

Date = \$(vDate)

and

( K2 > \$(vLimit_K2) or 3 < \$(vLimit_K3) )

;

/Teis

Not applicable

## Re: Aggr fuction with If Conditoins

the Limits are Variables, therefore you can not use them in the Loadscipt, can you?

MVP

## Re: Aggr fuction with If Conditoins

Can you try this:

Sum(Aggr(Sum(DISTINCT If(K1 > vLimit_K1 and Date = vDate and (K2 > vLimit_K2  or 3 < vLimit_K3)), KNE_IA), KN_ID))

Contributor II

## Re: Aggr fuction with If Conditoins

Sure u can

ex. in scrip

Set or Let depending on it is a string og formula

Set vLimit = XXXXXXXXX;

/teis

Not applicable

## Re: Aggr fuction with If Conditoins

okay, for the first numbers it works. I will have test a bit if the result is always correct, but it should be.

You really helped me.

Thank you very much, Sunny T. You are the best, as always

Community Browser