How to get Balance amount to a one row?

[Amanda_Tennakoon]

Hello, 

I am new to QlikSense and could you please help me out to find a way of doing the below-mentioned point?

The above report was generated through a separate view.

I need to get above one like this.

When we consider the highlighted couple of rows, it is under the same CUSTOMER_TRX_ID. It has an invoice and a receipt with a Dr Amount and Cr Amount. The formula is to calculate the Balance column is (Dr Amount - Cr Amount).

I need to get the Balance for the first row as zero and for the second row -58,675.00.

NOTE: There can be rows like more than one receipt for an invoice.

Could someone have an idea, how to perform this action?

[Digvijay_Singh · Accepted solution]

May be a measure like this, may be a little tweak needed around this -

Aggr(Sum(Dr Amount) - Sum(Cr Amount),CUSTOMER_TRX_ID)

[Amanda_Tennakoon]

Thank you so much and this worked.

But now, do you have any idea to remove the below duplicates?

I just need one row. But it is duplicating.

[stevejoyce · Accepted solution]

The default behavior is if all expressions = 0/null it will not display that row.

Option 1:  Remove the dimension that is causing duplicate rows (date or receipt type).  You can instead display those are a measure like using concatenate.

or Option 2:  If you have an expression that determines whether you want a row to display or not (i.e. "Balance" in your example), you can have all measures setup like:  If(not isnull([Balance], <expression>).  In this case, [Balance] is referring to the expression label.  This way it will make sure all expressions return null when balance is null.