topic Re: RangeSum with 3 Dimension in QlikView

RangeSum with 3 Dimension

qv_jagan — Fri, 11 Nov 2016 06:38:14 GMT

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.

Country	State	Branch	Value	Expected Value
C1	S1	B1	1	1
C1	S1	B2	2	3
C1	S2	B3	3	6
C2	S4	B4	4	4
C2	S5	B5	5	9
C3	S5	B6	6	15

Re: RangeSum with 3 Dimension

Chanty4u — Fri, 11 Nov 2016 06:42:26 GMT

try this

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

Re: RangeSum with 3 Dimension

Chanty4u — Fri, 11 Nov 2016 06:47:16 GMT

try this

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;

Re: RangeSum with 3 Dimension

qv_jagan — Fri, 11 Nov 2016 06:47:54 GMT

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

Re: RangeSum with 3 Dimension

Chanty4u — Fri, 11 Nov 2016 06:50:15 GMT

you can try full accumalation option in Expression tab

orelse try

Accumalate[2] stepback option below that

Re: RangeSum with 3 Dimension

qv_jagan — Fri, 11 Nov 2016 07:03:07 GMT

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.

Re: RangeSum with 3 Dimension

Chanty4u — Fri, 11 Nov 2016 07:06:32 GMT

you can try this in script

you can try full accumalation option in Expression tab

orelse try

Accumalate[2] stepback option below that

Re: RangeSum with 3 Dimension

qv_jagan — Fri, 11 Nov 2016 09:09:35 GMT

Hi Chan

Re: RangeSum with 3 Dimension

qv_jagan — Fri, 11 Nov 2016 09:12:52 GMT

Hi Chan,

RangeSum is calculated at Country Level.

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

Thanks

Jagan

Re: RangeSum with 3 Dimension

MK_QSL — Fri, 11 Nov 2016 09:23:49 GMT

Hi Jagan,

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

Re: RangeSum with 3 Dimension

rubenmarin — Fri, 11 Nov 2016 10:35:03 GMT

Hi Jagan, I created a document and it's working with your original expression, what isn't working for you?.

Are you doing this in a pivot table or in a straight table?

Re: RangeSum with 3 Dimension

rubenmarin — Fri, 11 Nov 2016 10:57:22 GMT

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.

Re: RangeSum with 3 Dimension

qv_jagan — Fri, 11 Nov 2016 14:19:15 GMT

That was a typo. Sorry for inconvenience.

This is what I was expecting.

Can you please explain how these 2 expression works.

RangeSum(Above(TOTAL Sum(Value), 0, Cont)) ---------------------> Correct (RangeSum Grouped at Country Level)

RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL))) -----> Full Accumulation Not Required.

Thank you so much for your help.

Re: RangeSum with 3 Dimension

rubenmarin — Mon, 14 Nov 2016 10:20:04 GMT

Hi Jagan,

RangeSum(Above(TOTAL Sum(Value), 0, Cont)

Cont is another expression on the chart, it's an incremental autonumeric that starts at 1 each time it founds a different country than the previous row, so each row knows how many rows of the same country are above (those rows have to be accumulated)

This column is hidden in the presentation tab, so it's only used to calculate another expression, not to show in chart.

RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL)))

RowNo(TOTAL) counts the number of rows, the TOTAL identifier specifies to ignore chart dimensions, so it counts all rows, without restarting in each country.

You can add an expression with just RowNo(TOTAL) and another with RowNo() to check the differences. The behaviour also changes between a straigh table and a pivot table.

Regards.