Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum of values based on distinct value, grouped by third value

Hi,

I'm trying to determine how to get the sum of [total outstanding by obligor] by distinct [obligor] where [total exposure by obligor] < 250,000 (Bucket "X"). I need to apply the same criteria for another expression where [total exposure by obligor] < 1,000,000 and [total exposure by obligor] > 5,000,000 (Bucket "Y").

Using the sample data below, my results should show Bucket X = 40,000 and Bucket Y = 900,000.

Some other answers lead me in this direction:Sum(Aggr(Sum(TOTAL_OUTSTANDING_BY_OBLIGOR), [OBLIGOR_NUM]))

But I'm struggling to figure out how to add the "if" to bucket the data and be sure it is distinct. Note that the "total by" columns have the same value for each row for a distinct obligor.

Thanks,

Steve

1 Solution

Accepted Solutions
Not applicable
Author

Thanks for the help - the suggestion helped me solve it (the division at the end is just to represent the total in thousands) :

<250 M expression:

Sum(if(Aggr(Max(TOTAL_EXPOSURE_BY_OBLIGOR), [OBLIGOR_NUM])<250000, Aggr(Max(TOTAL_OUTSTANDING_BY_OBLIGOR), [OBLIGOR_NUM])))/1000

250 M - 1 MM expression:

Sum(
if(Aggr(Max(TOTAL_EXPOSURE_BY_OBLIGOR), [OBLIGOR_NUM])>=250000 and Aggr(Max(TOTAL_EXPOSURE_BY_OBLIGOR), [OBLIGOR_NUM])<1000000, Aggr(Max(TOTAL_OUTSTANDING_BY_OBLIGOR), [OBLIGOR_NUM]))
)/1000

View solution in original post

5 Replies
sunny_talwar

What is Bucket X and Y in your database?

Not applicable
Author

Hi Sunny,

They are expressions I'm adding based on fields in the database. The fields are the names in brackets below - sorry I didn't make my Excel sample data names match exactly.

Bucket X is an expression representing the sum of [total outstanding by obligor] by distinct [obligor] where [total exposure by obligor] < 250,000.

Bucket Y is an expression representing the sum of [total outstanding by obligor] by distinct [obligor] where [total exposure by obligor] < 1,000,000 and [total exposure by obligor] > 5,000,000.

I hope that helps.

Thanks,

Steve

Not applicable
Author

Hi,

If the values are always the same for the [TOTAL_OUTSTANDING_BY_OBLIGOR], instead of having it summed you could use either AVG, MAX or MIN since, theoretically, they should all return the same distinct value - for example

Sum(Aggr(Avg(TOTAL_OUTSTANDING_BY_OBLIGOR), [OBLIGOR_NUM]))

Not applicable
Author

Hi Lee,

I think that might help, but I still need to group the sum of those values by [total exposure by obligor] - one total where the total exposure is under 250,000, another for 250,000-1 million another for 1-5 million, and finally greater than 5 million.

Thanks,

Steve

Not applicable
Author

Thanks for the help - the suggestion helped me solve it (the division at the end is just to represent the total in thousands) :

<250 M expression:

Sum(if(Aggr(Max(TOTAL_EXPOSURE_BY_OBLIGOR), [OBLIGOR_NUM])<250000, Aggr(Max(TOTAL_OUTSTANDING_BY_OBLIGOR), [OBLIGOR_NUM])))/1000

250 M - 1 MM expression:

Sum(
if(Aggr(Max(TOTAL_EXPOSURE_BY_OBLIGOR), [OBLIGOR_NUM])>=250000 and Aggr(Max(TOTAL_EXPOSURE_BY_OBLIGOR), [OBLIGOR_NUM])<1000000, Aggr(Max(TOTAL_OUTSTANDING_BY_OBLIGOR), [OBLIGOR_NUM]))
)/1000