Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How does AGGR function exactly work in case of missing values?

Hi Guys,

Perhaps you could help me here. I struggle to understand how exactly AGGR function works when I try to involve two aggr functions in one calculation.

Lets say I have the following formula:

aggr ( {< [Client TAG] = {'No'} >} sum( EBIT ) , ID , Year)

/

aggr ({ <[Client TAG] = {'No'} >} sum( Turnover ), ID , Year )


If there are no missing values this seems to work just fine and it gives a simple result (RESULT = outcome of the given formula)


aggr(EBIT)                aggr(TURNOVER)       RESULT

1                                 1                                  1

2                                 2                                  1       

2                                 1                                  2

4                                 1                                  4


However, if there would be a missing value lets say in second row in Turnover, how this would look like in the AGGR temp tables calculation?


aggr(EBIT)                aggr(TURNOVER)       RESULT

1                                 1                                  1

2                                 1                                  2       

2                                 1                                  2

4                                 -                                   -


OR


aggr(EBIT)                aggr(TURNOVER)       RESULT

1                                 1                                  1

2                                 -                                   -       

2                                 1                                  2

4                                 1                                  4

10 Replies
sunny_talwar

If the denominator of a expression is null, what else would you expect the output to look like?

Anonymous
Not applicable
Author

The fact that the outcome is NULL is obvious to me. I am rather asking about how will the further subtraction go.

I have a feeling that what happens is that it completely ignores the NULL values and if there is a NULL the temp table will have less rows and it can start subtracting wrong observations together

like in this example where the second ROW has missing turnover but in the end it is first three rows that get calculated due to the row three taking place at row 2.

aggr(EBIT)                aggr(TURNOVER)       RESULT

1                                 1                                  1

2                                 1                                  2      

2                                 1                                  2

4                                 -                                   -

sunny_talwar

The fact that the outcome is NULL is obvious to me. I am rather asking about how will the further subtraction go.

Further subtraction? Can you elaborate on this? I don't see any subtraction

Anonymous
Not applicable
Author

I mean division not subtraction. Nevertheless the point is the same even if it was subtraction.

sunny_talwar

Subtraction can be handled using RangeSum()

RangeSum(

Aggr({<[Client TAG] = {'No'}>} Sum(EBIT), ID, Year)

-Aggr({<[Client TAG] = {'No'}>} Sum(Turnover), ID , Year)

)

Division can only happen if you divide by 1. May be you can do this

Aggr({<[Client TAG] = {'No'}>} Sum(EBIT), ID, Year)

/

Alt(Aggr({<[Client TAG] = {'No'}>} Sum(Turnover), ID, Year), 1)

swuehl
MVP
MVP

There should be same number of rows for both nominator and denominator, because you are using same aggr() dimensions and set expression.

[edit: maybe this was a little too fast, it may depend on how your Turnover and EBIT field values are linked to ID and YEAR. ]

If in doubt, why not use

aggr ( {< [Client TAG] = {'No'} >} sum( EBIT ) /Sum(Turnover), ID , Year)

(and I would always consider using an outer aggregation function or explicitely stating Only() )

Anonymous
Not applicable
Author

Thank you Stefan,

This is the kind of answer that I was hoping for. The ID and Year is linked to both EBIT and Turnover in exactly same way, they are just 2 different values in 2 different columns (simplified explanation).

The problem here however is that it still does not explain my problems that I am experiencing with the further use of this function.

I am actually using this further in my calculation to calculate upper, lower fractile and median out of those values. And what I am experiencing is that an extension for boxplots (I am forced to use an extension as the native QlikSense boxplot does not offer an option to add one more linear measure to the chart) simply shows different values for these once there are missing values than a simple KPI measure.

The difference in calculation is that for boxplot I simply calculate the Aggr and let the chart do the outer function in terms of calculating min,max,median,lower and upper Q. And for KPI I do manually calculate Fractile(aggr function, 0.25) etc..

Picture showing same results, no missing value in any of the observation,

Pic1Aggr.png

Picture showing different results, missing values have been in some of the observations

Pic2Aggr.png

swuehl
MVP
MVP

Have you already tried to use a single Aggr() function as I suggested above?

Anonymous
Not applicable
Author

‌HI Stefan,

Yes but the result it rather the same the only benefit that i see i brongs so far is that we create less temp tables thus have better performance.

Mismatch in the results remains the same though