sum of the total of distinct file numbers

[#1]

Hi All,

expression Level :

i need to find the sum of the total Amount but for distinct file nos.

my expression used was this :

i got 2 expressions

first i calculate count :

=count(distinct if(IsNull(FrankingBarCode)=-1 and IsNull(ProductId)=-1 and IsNull(DisbursementDate)=-1 and IsCancel<>'Y',FrankingNumber))

then the Amount :

=sum(if(IsNull(FrankingBarCode)=-1 and IsNull(ProductId)=-1 and IsNull(DisbursementDate)=-1 and IsCancel<>'Y',TotalAmount))

count is correct but due to duplication of franking number the amount increases

How can i apply the Sum for distinct values or define distinct values inside the Sum

[#2]

can somebody help in this issue????

[jonathandienst]

Hi

Do the duplicated fields have the same TotalAmount value? Then this should work:

=Sum(Aggr(Distinct Only(if(IsNull(FrankingBarCode)=-1 and IsNull(ProductId)=-1 and IsNull(DisbursementDate)=-1 and IsCancel<>'Y', TotalAmount)), FrankingNumber))

HTH

Jonathan

[#4]

Hey Jonathan,

ThnkX for the help

I already tried Aggr function it gives correct values for some of the dimensions

but not for all,

it is skipping the TotalAmount for some of the FrankingNumbers

[jonathandienst]

Hi

I suspect that the precondition in my earlier post (Do the duplicated fields have the same TotalAmount value?) is not met. Only will return a null if the values differ, so Franking Numbers that are duplicated with differing amounts will return 0.

So then the question becomes: Which of the amount values do you wish to use?

Regards

Jonathan