topic Re: Sum of values below divided by sum of values one cell above and below in QlikView

Sum of values below divided by sum of values one cell above and below

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

Hi,

I have an excel file which has data like below

Data	Cleared Status	% Calc
Sourcing	10	0%
Scree Cl	20	0.933333
Int Cl	30	0.857143
Offer	40	0.75
Onboard	50

% calc is field I need to derive in qlikview.

The way it is calculated as is

(20+30+40+50)/(10+20+30+40+50)=0.933.. similarly (30+40+50)/(20+30+40+50)=0.8571.

I tried different combination of Rangesum function but it didn't work.

Please help. Attach is the file

Re: Sum of values below divided by sum of values one cell above and below

sunny_talwar — Tue, 13 Mar 2018 09:05:29 GMT

May be this

=Above(Sum(TOTAL [Cleared Status]) - RangeSum(Above(Sum([Cleared Status]), 0, RowNo())))/Above(Sum(TOTAL [Cleared Status]) - RangeSum(Above(Sum([Cleared Status]), 1, RowNo())))

Re: Sum of values below divided by sum of values one cell above and below

abhinavpathak17 — Tue, 13 Mar 2018 09:11:30 GMT

Perfect..Thanks