Aggregate Results

[qlikwiz123]

I have a Straight Table with the following Data

Item	Size	Unit	Stock	Unit Stock
				247,204
Shirt	Shirt8	8	14,100	112,800
Shirt	Shirt4	4	33,601	134,404

Unit Stock field is calculated by Unit*Stock

Expression for 'Unit' is 

Sum(Aggr( [Unit],[Size]))

Expression for 'Stock' is 

Aggr(Sum(Stock), Size)

But when I remove 'Size' from the table, the table is shown as 

Item	Size	Unit	Stock
			0
Shirt	12	-	-

How do I achieve the first table without showing 'Size' column?

If I remove aggr in 'Stock',  and use (Sum( Stock ), it is doing 12 (8+4) * 47701 (14100+33601) instead of  (8*14100) + (4*33601).

My end table should look like this :

Item	Stock
	247204
Shirt	247204

where Stock is calculated as (8*14100) + (4*33601).

[sunny_talwar · Accepted solution]

Try this

Sum(Unit * Stock)

or this

Sum(Aggr(Sum(Unit * Stock), Item, Size, Unit))

[qlikwiz123]

Hi @sunny_talwar 

I am using expression

Sum({$<Date = {"$(=Date(vMaxDate, 'MM/DD/YYYY'))"}>} [Stock]) for calculating my Stock as I need to see only at Stocks for recent date. I am not sure if this is causing an issue but when I remove Size, Unit, the Stock column shows '-' 

[sunny_talwar]

Not sure why that would happen... the expression you shared is the only thing you are using, right? no aggr() or if statements or anything else?

[qlikwiz123]

To calculate the stock, yes. Sum({$<Date = {"$(=Date(vMaxDate, 'MM/DD/YYYY'))"}>} [Stock]) is how I find the Stock for the latest date and doing a Sum on it. Nothing else.

[sunny_talwar]

Would you be able to share a sample where we can see the issue?

[qlikwiz123]

I think I got what the issue is. I had to implement the logic I was using in the script instead of the chart and that fixed it. Weird but true.

Thank you so much @sunny_talwar  🙂