topic Re: wrong aggr sum in QlikView

wrong aggr sum

Thu, 22 Nov 2012 12:57:03 GMT

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

Re: wrong aggr sum

Gysbert_Wassenaar — Thu, 22 Nov 2012 13:06:34 GMT

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

Re: wrong aggr sum

Thu, 22 Nov 2012 13:26:08 GMT

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?

Re: wrong aggr sum

Gysbert_Wassenaar — Thu, 22 Nov 2012 13:37:05 GMT

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))

Re: wrong aggr sum

Fri, 23 Nov 2012 05:49:13 GMT

thank you again