topic Re: Range sum above in QlikView

Range sum above

Loreen — Tue, 30 Apr 2019 06:49:36 GMT

Hello,

I try to get the result of the total mix quantity column, like below :

Product n°	MIX QUANTITY	TOTAL MIX QUANTITY
	10358
1	1966	10398
1	1966	10398
1	1966	10398
2	1500	10398
2	1500	10398
2	1500	10398

The column total mix quantity is the sum of rows of the mix quantity column. My column MIX QUANTITY is an expression. For the total mix quantity column, I tried with Rangesum($(v_Mixqty)) and it gives me the result of the column mix quantity.

I tried to create an expression with rangesum and above but it always gives me 0 on every rows. Do you have an idea to solve my problem ?

Thanks in advance

Loreen

Re: Range sum above

tresB — Tue, 30 Apr 2019 07:06:01 GMT

I guess easier way would be to use TOTAL qualifier in expression. Say, your mix quantity result comes using expression Sum(quantity), then for total column you could use Sum(Total quantity)

Re: Range sum above

Loreen — Tue, 30 Apr 2019 07:24:27 GMT

Hi,

Thanks for your answer. I already tried with Sum(total($(v_Qtymix)) but it doesn't work. There are "-" on every row. I think it is because Qty mix is an expression.

I also tried with Sum total aggr, it gives me the same value on every rows (it's what I want) but the result it's not the good one

Re: Range sum above

tresB — Tue, 30 Apr 2019 07:26:36 GMT

How is your variable v_Qtymix defined?

Re: Range sum above

Loreen — Tue, 30 Apr 2019 07:42:29 GMT

v_Qtymix :

Sum(Aggr(NODISTINCT($(v_Qty_17salesmen),Material_Code))

$(v_Qty_17salesmen) : It's a variable calculated with %qty plus a context that makes me filter the salesmen that I want

here is the expression that I have in my excel file :

Sum({$<{CTXT}, SalesRep=, SalesRep_Code=,SalesRep_Code={'S10','S11','S12','S14','S15','S16','S19','S21','S22','S23','S24','S29','S31','S32','S33','S34','S39'}>} %Qty)

Re: Range sum above

tresB — Tue, 30 Apr 2019 07:49:42 GMT

Try:
Sum( TOTAL Aggr(NODISTINCT($(v_Qty_17salesmen),Material_Code))

Re: Range sum above

Loreen — Tue, 30 Apr 2019 08:29:31 GMT

Thanks you very much for your reply,

I tried

Sum(TOTAL<SalesRep>Aggr(NODISTINCT(Sum(TOTAL<Material_Code>AGGR($(v_Qtymix),SalesRep,Material_Code))),Material_Code))

The result is almost good. The only issue is that my filter on sales men doesn't work, the calcul takes all the salesmen into account

Re: Range sum above

kushalthakral — Tue, 30 Apr 2019 13:10:55 GMT

Hi Can you please share your sample QVW file

Thanks
Kushal