topic Re: Aggr and Total Function in QlikView

Aggr and Total Function

Thu, 22 Jan 2015 16:07:17 GMT

I have the following pivot in qlikview.

JS	Qty	Unit cost	Amount
JS-001	10	100	1000
JS-001	15	110	1650
JS-001	20	165	3300
JS-002	25	330	8250
JS-002	15	330	4950
JS-002	20	20	400
JS-002	20	20	400
JS-003	25	14	350
JS-003	26	56	1456
JS-003	67	23	1541
JS-003	89	98	8722

Column Amount is Output or calculated field (i.e unit cost * Qty)

I want another table as follows:

JS-001	5950
JS-002	14000
JS-003	12069

5950 is the sum total of amounts of JS-001. because it is a calculated field, i am not able to prepare another table with the output from the previous table. Can anyone help me. it will be great if i get the answer in qlikview file.

Re: Aggr and Total Function

jerem1234 — Thu, 22 Jan 2015 16:12:01 GMT

Use only the field JS as a dimension in the straight table. Then use something like sum(Amount) or whatever you have for the expression.

Maybe the expression can be:

sum(aggr(Qty*[Unit cost], JS, Qty, [Unit cost]))

Hope this helps!

Edit: You can also move the calculation of Amount in the script, so it does not need to be calculated in the dashboard. Maybe a field like: Qty*[Unit cost] as Amount

Re: Aggr and Total Function

alexandros17 — Thu, 22 Jan 2015 16:14:47 GMT

this is your changed document:

let me know