topic Re: Aggregate Results in QlikView

Aggregate Results

qlikwiz123 — Fri, 15 Nov 2024 23:57:38 GMT

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

Re: Aggregate Results

sunny_talwar — Thu, 17 Sep 2020 13:13:15 GMT

Try this

Sum(Unit * Stock)

or this

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

Re: Aggregate Results

qlikwiz123 — Thu, 17 Sep 2020 15:31:17 GMT

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

Re: Aggregate Results

sunny_talwar — Thu, 17 Sep 2020 16:58:54 GMT

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?

Re: Aggregate Results

qlikwiz123 — Thu, 17 Sep 2020 17:04:34 GMT

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.

Re: Aggregate Results

sunny_talwar — Thu, 17 Sep 2020 17:59:52 GMT

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

Re: Aggregate Results

qlikwiz123 — Fri, 18 Sep 2020 12:18:11 GMT

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 🙂