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

    Aggr fuction with If Conditoins

    Thorben Ortmann

      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