Problem grouping SUMS using Queries

attieffe

Problem with Nav C/AL Queries

Hello,

i'm using NAV 2017 and i attempt to the the following (the real case is more complex but i just ask for a specific function):

I have a table Items and i want to use a Query that give me bak ONE record for ITEM, with some static field and some computed fields:
- purchased quantity (sum of quantity from Item Ledger Entry with TYPE PURCHASE OR TRANSFER, in a specific period and location filters)
- purchased value (sum of COST AMOUNT from Item Ledger Entry with TYPE PURCHASE OR TRANSFER, in a specific period and location filters)
- sale quantity (sum of quantity from Item Ledger Entry with TYPE SALE, in a specific period and location filters)
- sale value (using the purchase value not the sell one) (sum of COST AMOUNT from Item Ledger Entry with TYPE PURCHASE OR TRANSFER, in a specific period and location filters)

So i setup a Query with 3 level:
level 1: ITEM
level 2: ILE_BUY linked by fields on ITEM table, datefilter, location filter, Types Purchase/Transfer
level 3: ILE_SALE linked by fields on ITEM table, datefilter, location filter, Type Sale

Now the problem:
There is one record on table ITEM,
ONE record of Purchase TYPE and quantity 2
TWO record of Sale type with quantity -1 / -1

Running the query i found:
Purchase quantity amount => 4 (2 expected)
Sale quantity amount => -2



So the problem is that it brings those 2 PURCHASED quantity on EACH SALE record, so it SUMS 2 times 2 quantity.

I tried moving both Item ledger entry at the same LEVEL it says me that is not possible.

If I remove all the SALE part on the design the Purchase quantity is correct => 2

I post an image of my current setup.






All relations between tables have DataItemLinkType Exclude Row If No Match (i tried also other values but the results is still the same).

Thank you
Attilio