Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
abhinavpathak17
Contributor
Contributor

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

Hi,

I have an excel file which has data like below

  

   

DataCleared Status% Calc
Sourcing100%
Scree Cl200.933333
Int Cl300.857143
Offer400.75
Onboard50

% 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

1 Solution

Accepted Solutions
sunny_talwar

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


Capture.PNG

View solution in original post

2 Replies
sunny_talwar

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


Capture.PNG

abhinavpathak17
Contributor
Contributor
Author

Perfect..Thanks