topic Re: Remove Duplicate from Total in QlikView

Remove Duplicate from Total

bhaskarsharma03 — Thu, 06 Nov 2014 11:28:06 GMT

Hi,

I am preparing a sales report where:

1) Some order IDs are repeated so that the credit for Sales is given equally to two or more regions (ID 123 and 128 in the example)

2) While showing Sales for individual Regions, (in case of repeated Order ID) credit should be given to both the Regions

3) The Total section however shows the Total after eliminating the duplicates

Could you please have a look at the attached application and image and help me achieve it.

Regards,

Bhaskar

MK_QSL — Thu, 06 Nov 2014 11:35:10 GMT

As per your requirements, what should be the TOTAL value?

anbu1984 — Thu, 06 Nov 2014 11:41:33 GMT

Check this app

MK_QSL — Thu, 06 Nov 2014 11:53:19 GMT

IF(Dimensionality() = 0, SUM(Aggr(SUM(Distinct Sales),OrderID)), SUM(Sales))

Thu, 06 Nov 2014 12:05:12 GMT

Do you want to eliminate the duplicates values ( For OrderID)from the total sales? Please confirm.

if not the above is absolutely right

Kushal_Chawda — Thu, 06 Nov 2014 12:31:22 GMT

use below expression

sum(aggr(sum(DISTINCT Sales),OrderID))