12 Replies Latest reply: Sep 2, 2016 4:27 AM by Thorben Ortmann

# 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

• ###### 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

• ###### 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.

• ###### 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

• ###### Re: Aggr fuction with If Conditoins

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

• ###### 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

• ###### Re: Aggr fuction with If Conditoins

But if I change the variable Values in the app after running the script, the fields, which I calculated in the script, would not Change their values depending on the actual variable value or would they ?

• ###### 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)

• ###### 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.

• ###### 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))

• ###### 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

• ###### Re: Aggr fuction with If Conditoins

Its difficult to know without testing it out. So I think testing on your end would be important. Let us know what you find

• ###### Re: Aggr fuction with If Conditoins

After Testing it, I can confirm that your solution works to solve the Problem I described.

Again thanks a lot for that.

Unfortunately I just got a new requirement for which this solution will not work, because I have to sum a different Field than KN_IA, that differs in its values for KN_ID.

I have made a new post on that stating the Problem in more detail .