Sum+Distinct

[My_Rebecca]

I have data from SAP, with SO Number and Total Package in Delivery.

I need to firstly calculate the unduplicated SO number, then sum the packages, but the expression fails to work.

How to correct it? Thanks.

=sum(distinct([SO Number])[Total Packages in Delivery])

Following is what the correct result would sum total package.

[ogster1974]

Add a row count to your table: 1 as RowCount

then sum(RowCount) 

[My_Rebecca]

@ogster1974  Could you please write expression in Qlik, not in excel? Thanks.

[justISO · Accepted solution]

Hi, how about something like this:

sum(aggr([Total Packages in Delivery], [SO Number]))

[ogster1974]

I was suggesting you add it to your load script not in excel.  

Add 1 as RowCount into your table load after the last field.

that will add a 1 to each row in your transaction table so you can derive your [Total Packages in Delivery] as a measure using sum(RowCount) 

Or in your load script if they're the only fields you are bringing through from that table.

Load YourTmpTable from source. 

YourTable:

Load

Distinct

[SO Number],

[Total Packages in Delivery]

Resident YourTmpTable;

drop table YourTmpTable;

[My_Rebecca]

@justISO  It doesn't work. Is there any other ways?

[My_Rebecca]

@ogster1974 Thanks for you suggestion, but I don't know how to write the script in load editor. I want to realize it in expression.

[ogster1974 · Accepted solution]

Try

sum(aggr(only([Total Packages in Delivery]), [SO Number]))

[My_Rebecca]

@ogster1974  It fails to reach the correct answer as I try it.

[My_Rebecca]

@ogster1974 sorry, it's my mistake. it works perfectly.