Totals should account for duplicates

[rasmusnielsen]

Hi guys,

My data looks like:

A single transaction is made up of some volume number and 1 or more payment IDs

My table show have a row per transaction and payment ID, where the volume is "duplicated" for each payment ID of the transaction. But the totals should account for this and only sum up the transaction volume and thus ignore "duplicated" entries. I my table above, I average the volume for the transaction to account for the "duplicated" volume.

In the table above, I would like the rows to have the values of the last column, but the total to be that of the 2nd last column. If I add nodistint to the Aggr function, I get the expected row values, but the total will be wrong. Also tried changing the totals function from auto to sum, but it did not help.

Any hints on how to solve this?

Thanks

[Kushal_Chawda]

@rasmusnielsen  You can use sum(distinct Volume) with aggr function. Try something below

=sum(aggr( sum(distinct Volume),TransactionID))

[kevincase]

Based on your dimensions, this is how Qlik works.  If you want to limit the data, flag the rows you want to display in your load script.

[rasmusnielsen]

@Kushal_Chawda That gives me the same result as my initial approach with avg

[Kushal_Chawda · Accepted solution]

@rasmusnielsen  you can probably change the expression for total using dimensionality()

if(Dimensionality()=0,sum(aggr( sum(distinct Volume),TransactionID)), sum(Volume))

[rasmusnielsen]

@Kushal_Chawda Thanks, that did the trick: