wrong aggr sum

[#1]

Hi

i have data table and exprasion :

aggr(sum(Quantity_in_deliver),Affilietedid,goodsid,amount_in_invoice)

this table is good and all data has right grouping

but when i try to sum my aggragated data like this:

sum(aggr(sum(Quantity_in_deliver),Affilietedid,goodsid,amount_in_invoice))

it dosent work

please need help

sea atachments

[Gysbert_Wassenaar]

If you do a sum(distinct Quantity_in_deliver) you'll get the total you want.

[#3]

hi!

i have table with data from 2 different types of document

if you look exel file on goog_5:

Invoice has onli 2 rows from good_5: good_5 with 2,265  and good_5 with 2,84. Sum Quantity_in_invoice =

2,265 + 2,84 = 5.105

and the same position in document has 3 rows: 5 with 0,006 and good_5 with 2,265 and good_5 with 2,834 Sum Quantity_in_deliver = 0,006 +  2,265 + 2,834 = 5.105

Left join 2 documents give me 6 rows

and when i use aggr(sum(Quantity_in_deliver),Affilietedid,goodsid,amount_in_invoice) it give me right grouping

if i use sum(distinct Quantity_in_deliver) sum by Customer_A became = 43.79 when right sumis 43.8

I see problem with data and think what if a can put all rows from aggr(sum(Quantity_in_deliver),Affilietedid,goodsid,amount_in_invoice) into temp dimention and then sum all elements

is it posseble?

[Gysbert_Wassenaar · Accepted solution]

Hmm yeah, a simple distinct Quantity_in_invoice isn't enough. It needs to take some dimensions into account.

This seems to work:

sum(aggr(sum(DISTINCT Quantity_in_invoice),goods,invoice_number,bg_t,Affilietedid, Customer))

[#5]

thank you again