Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Sum if Distinct

I am trying to sum the transaction amount if the trans type field is ACH Return and it has a distinct loan number.  My expression is not correct and I am not sure the best way to do this.

Thanks in advance!!

Kristy

sum({$*<[TranType]="*ACH Return*"}DISTINCT(LoanNbr)>})[Tran Amt]

1 Solution

Accepted Solutions
MVP
MVP

Re: Sum if Distinct

OK, so it looks like you want the Tran Amt from the FIRST time you encounter a given LoanNbr in your data set in the sequence you loaded it.  In the attached, I load an ID field from recno() during the initial load.  Then I left join to it with an order by, allowing me to set a "First?" flag.  Then I use set analysis to only look at the first row for a given loan number. 

LOAD *
,recno() as ID
INLINE [
LoanNbr Tran Type Tran Amt
1037553 ACH Return  176.47
...
] (delimiter is ' ');
LEFT JOIN (Data)
LOAD ID
,if(LoanNbr<>previous(LoanNbr),1) as First?
RESIDENT Data
ORDER BY LoanNbr, ID
;

sum({<First?={'1'}>} "Tran Amt")

8 Replies
Not applicable

Sum if Distinct

I am getting closer.  I now have the expression below.  Something is still not right though.  I only want to total the Tran Amounts for all of the ACH Return Tran Types that have distinct loan numbers.  Can anyone help?expression for question.bmp

MVP
MVP

Re: Sum if Distinct

I'm not sure I understood.  Maybe this?

sum({<[Tran Type]*={"*ACH Return*"}>} aggr(if(count(distinct LoanNbr)=1,[Tran Amt]),[Tran Type]))

Not applicable

Sum if Distinct

Thanks John,

I tried that expression but got $0's.  My spreadsheet has a ton of different Trans Types.  I want to sum the ACH Return transaction amounts, but there are multiple loan numbers.  I only want to sum the tran amounts for the ACH returns with distinct loan numbers. (However, I do want to keep multiple loan numbers in for the other types).  Any suggestions?  I have searched for other issues and you have unknowingly helped with those - so i know if anyone can help, you can!

MVP
MVP

Sum if Distinct

Sorry, I guess I just don't understand what you want.  Perhaps post an example file with an inline load for the data and a chart showing the results you want.

MVP
MVP

Re: Sum if Distinct

Attached is my guess of what you want.  It has two solutions, one using a calculated dimension, one using an expression.  In it, Tran Type "ACH Return A" isn't in the chart because it's an ACH return with more than one loan number associated with it.

Not applicable

Sum if Distinct

The 1st expression gave me $7m figure (i am looking for a 200k one).  The 2nd expression came up null.  I have attached a spreadsheet with the full data and the number I want to get to.  I have the tran type filtered, so its only ACH Returns already. 

Not applicable

Re: Sum if Distinct

The 1st expression gave me $7m figure (i am looking for a 200k one).  The 2nd expression came up null.  I have attached a spreadsheet with the full data and the number I want to get to.  I have the tran type filtered, so its only ACH Returns already. 

MVP
MVP

Re: Sum if Distinct

OK, so it looks like you want the Tran Amt from the FIRST time you encounter a given LoanNbr in your data set in the sequence you loaded it.  In the attached, I load an ID field from recno() during the initial load.  Then I left join to it with an order by, allowing me to set a "First?" flag.  Then I use set analysis to only look at the first row for a given loan number. 

LOAD *
,recno() as ID
INLINE [
LoanNbr Tran Type Tran Amt
1037553 ACH Return  176.47
...
] (delimiter is ' ');
LEFT JOIN (Data)
LOAD ID
,if(LoanNbr<>previous(LoanNbr),1) as First?
RESIDENT Data
ORDER BY LoanNbr, ID
;

sum({<First?={'1'}>} "Tran Amt")

Community Browser