topic Re: Calculating total in QlikView

Calculating total

ccsimo16 — Wed, 25 Nov 2020 16:16:04 GMT

I have a spreadsheet which looks like

Supplier Tier Value Rebate

A 1 100 1%

B 2 200 5%

C 1 50 1%

D 2 300 2%

I can calculate each line with no problem by using

=sum (value) * sum(Rebate)

The problem comes when i want to total all rebate by Tier

Thanks

Re: Calculating total

prma7799 — Fri, 22 Dec 2017 08:39:18 GMT

Try like this

Test:

LOAD * , SubField(Rebate,'%',1) as Rebate_New;

LOAD * Inline [

Supplier, Tier , Value ,Rebate

A, 1 , 100 , 1%

B , 2 , 200 , 5%

C , 1 , 50 , 1%

D , 2 , 300 , 2%

];

Re: Calculating total

ccsimo16 — Fri, 22 Dec 2017 08:45:11 GMT

Hi PM

Thanks for the reply, how would i write the Expression

Thanks

Chris

Re: Calculating total

marcus_sommer — Fri, 22 Dec 2017 08:48:21 GMT

Try it with the following expression:

sum(aggr(sum(value) * sum(Rebate), Supplier, Tier))

- Marcus

Re: Calculating total

Anil_Babu_Samineni — Fri, 22 Dec 2017 08:49:45 GMT

Do you mean this?

=sum (TOTAL <Tier> value) * sum(TOTAL <Tier> Rebate)

sum(aggr(sum(value) * sum(Rebate), Supplier, Tier))

Re: Calculating total

ccsimo16 — Fri, 22 Dec 2017 09:01:14 GMT

Thanks All Great Community as always

Re: Calculating total

Anil_Babu_Samineni — Fri, 22 Dec 2017 09:02:54 GMT

You can mark proper correct answer

Re: Calculating total

prma7799 — Fri, 22 Dec 2017 09:23:23 GMT

Like this??

sum(Aggr(sum(value) * sum(Rebate_New), Supplier, Tier))

Re: Calculating total

ccsimo16 — Fri, 22 Dec 2017 09:23:58 GMT

Hi Anil

it was sum(aggr(sum(value) * sum(Rebate), Supplier, Tier))

How would i change to work as a dimension?

Thanks

Re: Calculating total

Anil_Babu_Samineni — Fri, 22 Dec 2017 09:31:16 GMT

May be remove Sum(), Perhaps like below?

Aggr(sum(value) * sum(Rebate), Supplier, Tier)

Re: Calculating total

marcus_sommer — Fri, 22 Dec 2017 09:32:27 GMT

It would work without the outer-aggregation like:

aggr(sum(value) * sum(Rebate), Supplier, Tier)

whereby it might be useful to round and or format it or maybe to wrap it with a class-function like:

class(aggr(sum(value) * sum(Rebate), Supplier, Tier), 5)

- Marcus