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: 
Ja123__
Partner - Creator
Partner - Creator

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

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 )

)

MemberLocationAgeSum of Contributions

123

SA28100
234ZA2950
556ZA30-
291SA3196

 

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

 

 

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

May be try this

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

View solution in original post

2 Replies
sunny_talwar

May be try this

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

Ja123__
Partner - Creator
Partner - Creator
Author

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 )