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

[#1]

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

[sunny_talwar]

What is Bucket X and Y in your database?

[#3]

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

[#4]

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

[#5]

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

[#6 · Accepted solution]

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