Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
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

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

View solution in original post

12 Replies
Partner
Partner

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

Partner
Partner

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:

Load

     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?

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

View solution in original post

Partner
Partner

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