Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

wrong aggr sum

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

Tags (1)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: wrong aggr sum

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


talk is cheap, supply exceeds demand
4 Replies
MVP & Luminary
MVP & Luminary

Re: wrong aggr sum

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


talk is cheap, supply exceeds demand
Not applicable

Re: wrong aggr sum

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?

MVP & Luminary
MVP & Luminary

Re: wrong aggr sum

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


talk is cheap, supply exceeds demand
Not applicable

Re: wrong aggr sum

thank you again

Community Browser