Sum of members if overall client balance greater than 0. Aggr, IF, SUM and multiple conditions.

[Ja123__]

Hi Guys

I have thought of an example to ask that I think will answer some other questions I have.

I have members, members belong to clients. I want to check if at a client level there have been any payments in the previous month along with a few other criteria that I include in the models set analysis.

I want to display this calculation on a member detail level but it doesn't look right to me...

This is the calculation that I'm using and I will make notes below it, maybe something stands out. Specifically the last amount that I want to be the result.

sum(

if(
aggr(
sum(
{<
txtDescription12INDICATOR = {'AVC'},
dteEffectiveDate11 = {"<=$(vStartOfEndDate)>=$(vEndDatePrevMonth)"}, 
ExitTypeINDICATOR = {'-1'} 
>}
numAmount + numAmountInt ),ClientID)
> 0 ,

numAmount + numAmountInt

, 0 )

)

Member	Location	Age	Sum of Contributions
123	SA	28	100
234	ZA	29	50
556	ZA	30	-
291	SA	31	96

In a sentence: I want to check whether there are any amounts for specific criteria at a client level, and then display the amount that is applicable to that Member level, which is the lowest level.

I hope that I have explained this in enough detail.

James

[sunny_talwar · Accepted solution]

May be try this

Sum({<ClientID = {"=Sum({<txtDescription12INDICATOR = {'AVC'}, dteEffectiveDate11 = {[<=$(vStartOfEndDate)>=$(vEndDatePrevMonth)]}, ExitTypeINDICATOR = {'-1'}>} numAmount + numAmountInt) > 0"}>} numAmount + numAmountInt)

[Ja123__]

Thanks Sunny, I have seen I have a slightly different criteria and am only doing this on a member level but this approach above still helps me get to the answer!

Using the MembersID = ( sum (with criteria) > 0 )