Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
qv_jagan
Contributor

RangeSum with 3 Dimension

Hi,

I have below data. Column 5 is the expected value required.

I tried using RangeSum it is not working.

Dimension: Column 1-4

Expression 1: Sum(Value)

Expression 2: RangeSum(Above(Total Sum(Value),0,RowNo(TOTAL)))

Can some please help.

     

CountryStateBranchValueExpected Value
C1S1B111
C1S1B223
C1S2B336
C2S4B444
C2S5B559
C3S5B6615
1 Solution

Accepted Solutions

Re: RangeSum with 3 Dimension

Hi, I spotted the difference, now I have the same doubt as Manish, why that 15? shouldn't be '6' (it's a new country so it starts again)?

I attach a new document with this behaviour, I used a hidden column to calculate how many rows of the same country has to sum the above() function.

View solution in original post

13 Replies
Chanty4u
Esteemed Contributor III

Re: RangeSum with 3 Dimension

try this

RangeSum(Above(Sum(Marks), 0, RowNo()))

Chanty4u
Esteemed Contributor III

Re: RangeSum with 3 Dimension

try this

a:

LOAD Country,

     State,

     Branch,

     Value

     //[Expected Value]

FROM

[https://community.qlik.com/thread/239515?sr=inbox&ru=69692]

(html, codepage is 1252, embedded labels, table is @1);

Final:

NoConcatenate

Load Country,

     State,

     Branch,

    // Value,

     If(Peek(Country)=Country,PEEK('Value'),0)+Value as Value

Resident a Order by Country;

    

Drop Table a;

sul.PNG

qv_jagan
Contributor

Re: RangeSum with 3 Dimension

I doesn't work. Sum(Value) and RangeSum(Above(Sum(Marks), 0, RowNo())) provides same result set.

Chanty4u
Esteemed Contributor III

Re: RangeSum with 3 Dimension

you can try full accumalation option in  Expression tab

orelse try

Accumalate[2]  stepback option below that

qv_jagan
Contributor

Re: RangeSum with 3 Dimension

Hi Chanty,

The script was working fine for the above data. It doesn't work fine for the below data.

Also I need this to be written in Chart. Please help.

Capture.JPG

Chanty4u
Esteemed Contributor III

Re: RangeSum with 3 Dimension

you can try this in script

you can try full accumalation option in  Expression tab

orelse try

Accumalate[2]  stepback option below that

qv_jagan
Contributor

Re: RangeSum with 3 Dimension

Hi Chan

qv_jagan
Contributor

Re: RangeSum with 3 Dimension

Hi Chan,

RangeSum is calculated at Country Level.

Full Accumulation or Accumulate 2 steps back won't work for this scenario.

Thanks

Jagan

MVP
MVP

Re: RangeSum with 3 Dimension

Hi Jagan,

Can you explain the logic behind getting 15 for last row?