topic Re: Rangesum complex (?) issue in QlikView

Rangesum complex (?) issue

oxiofrt — Tue, 03 Nov 2015 21:16:37 GMT

Hi,

I have created a table containing a limited number of columns.

Sort order is important in that table :

1 : weight

2 : order (useful in case of equality of weight)

The Qlikview document herejoined contains the table with a few date, and a screenshot detailing what is the expected result.

I have tried several ways to do it, without success. It probably needs to use rangesum function but I don't know how to specifiy the dimensions on which the rangesum should be applicated.

Thank you for your help !

Re: Rangesum complex (?) issue

sunny_talwar — Tue, 03 Nov 2015 21:40:09 GMT

Something like this:

Re: Rangesum complex (?) issue

varunreddy — Tue, 03 Nov 2015 21:45:25 GMT

Sunny,

rangesum(below(sum(weight),1,Rowno()))

In this expression, you specified 1. does the sum start from current row. what if we specify 0 and what rowno() doing?

We have 10 , 8, 0. How are we getting 0?

Regards,

Varun

Re: Rangesum complex (?) issue

swuehl — Tue, 03 Nov 2015 23:49:35 GMT

Or maybe

=aggr( rangesum(below(sum(WEIGHT),1,NoOfRows())), AGENT, ORDER)

allowing you to resort the straight table, with the drawback that the ORDER field needs load order like requested sort order (which is the case in your sample qvw).

Re: Rangesum complex (?) issue

oxiofrt — Wed, 04 Nov 2015 17:42:15 GMT

Thank you very much Sunny, we are close to the solution.

There's something I miss : why doesn't it work anymore as soon as I add the WEIGHT column within the table ? (all rangesum values are turning to zero)

Didn't succeed to solve it...

Thanks

Re: Rangesum complex (?) issue

sunny_talwar — Wed, 04 Nov 2015 17:56:33 GMT

Add WEIGHT and ORDER as expressions rather than dimension in your chart and see if that works.

Re: Rangesum complex (?) issue

oxiofrt — Wed, 04 Nov 2015 18:16:32 GMT

Well, I had simplified the table, not imaginating that dimensions could be a problem.

You will find herejoined the table with the dimension I would like to display in that table, including an additional variable which is used in a test.

Adding that dimensions is causing the problem. !

Re: Rangesum complex (?) issue

sunny_talwar — Wed, 04 Nov 2015 18:18:16 GMT

Have you looked at swuehl‌ response? I have not checked it, but I have a feeling that his might not be affected by adding another dimension.

Re: Rangesum complex (?) issue

oxiofrt — Wed, 04 Nov 2015 18:20:50 GMT

Yes I did the the sorting issue is a real drawback.

I hoped to manage to avoid it 🙂

Re: Rangesum complex (?) issue

sunny_talwar — Wed, 04 Nov 2015 18:29:06 GMT

Adding the dimension will be problematic because if you add dimensions you will need to use Aggr function and Aggr function will sort based on the sort order even if I tell it to no do that. I think you have two options

1) Go by Stefan's method and sort in the script

2) Use all your other dimensions as expressions

If I were you, I will spend some extra time getting it done through method 1.

Re: Rangesum complex (?) issue

swuehl — Wed, 04 Nov 2015 18:43:10 GMT

Is the result depending on selections you make? If not, maybe it would be best to do the complete calculation in the script.

Meanwhile, I was wondering if our calculation is correct at all. You are telling in your qvw, that

we need to sum values for agent 2012123 (in first line) where weight >=10 and order >14.

Why do you add C7 (weight = 😎 then?

Re: Rangesum complex (?) issue

oxiofrt — Wed, 04 Nov 2015 19:39:12 GMT

Well I may have been clearer...

The aim is to sum for each agent the column WEIGHT, only for lines where ORDER is > to the ORDER value of the current line.

For agent 2012123 and line 1, it shoud sum all WEIGHT values for this agent where ORDER is > 14.

For agent 2012123 and line 5, it shoud sum all WEIGHT values for this agent where ORDER is > 15.

Result has to be calculated according to sélections.

Thanks a lot for all the precious help provided.

Re: Rangesum complex (?) issue

swuehl — Wed, 04 Nov 2015 20:31:58 GMT

Ok.

Does my above suggested solution works for you?

Sorting the load order should not be a big issue, it's enough to create a table first in the script with all possible ORDER values:

TMP: // table must appear in your script before you load ORDER from your sources

LOAD recno() as ORDER

Autogenerate 1000; // assuming range ORDER from 1 to max 1000

Then execute your script. You can even DROP TABLE TMP; at the end of the script, if you want.