Aggr fuction with If Conditoins

[#1]

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

[teiswamsler]

Hi Thorben

Is all of your data in one table?

What fields do you expect as dimension and measure?

/Teis

[Gysbert_Wassenaar]

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)

[#4]

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.

[#5]

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.

[teiswamsler]

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:

Load

     KN_ID,

     KN_IA

Resident DataTable1

     Where

          K1 > vLimit_K1

          and

          Date = $(vDate)

          and

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

;

/Teis

[#7]

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

[sunny_talwar · Accepted solution]

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

[teiswamsler]

Sure u can

ex. in scrip

Set or Let depending on it is a string og formula

Set vLimit = XXXXXXXXX;

/teis

[#10]

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