Totals with NODISTINCT

[a_yershov]

Hello, everybody!

Sorry for a novice question, but I've been stuck at this for several days and can't figure it out on my own.

We've got a source table like this (very simplified, of course):

Store	SKU	Store_Stock	WH_Stock
Store_1	Item_1	8	10
Store_1	Item_2	3	20
Store_2	Item_1	4	10
Store_2	Item_3	5	30
Store_3	Item_2	6	20
Store_3	Item_3	3	30

It contains the stocks of goods at different stores as well as their stocks at the main warehouse (whence the stores are supplied). The values for the warehouse stocks are repeated for each store there the item is present.

So, we need to caculate total stocks across the stores as well as the total stocks at the warehouse - to evaluate how much we can resupply the stores at best. The final table should look like this:

Store	Stock_at_Store	Stock_at_Warehouse
Store_1	11	30
Store_2	9	40
Store_3	9	50
Total	29	60

But I can't get such figures in QlikView. The stocks at the stores are calculated correctly, but the total for the warehouse stock is doubled:

Store	Stock_at_Store	Stock_at_Warehouse
Store_1	11	30
Store_2	9	40
Store_3	9	50
Total	29	120

For warehouse stock I'm using the expression:

Sum(aggr(NODISTINCT max(WH_Stock),SKU))

Without NODISTINCT the total becomes correct, but warehouse stocks across stores become wrong (naturally).

What am I doing wrong? How can this result be achieved?

Sample QV file is attached.

Many thanks in advance!

[Arthur_Fong · Accepted solution]

Try this:

if(Dimensionality()=0,Sum(aggr( max(WH_Stock),SKU)),
Sum(aggr(NODISTINCT max(WH_Stock),SKU))
)

[a_yershov]

That works, thank you very much!