Partial sum in a table

[#1]

Hi to all,

I have a problem to calculate a partial sum in a simple table. I need to sum (for example) the first 3 values, excluding the last 2.

The table is:

1st col. id

2nd col tariffa

ID TARIFFA

160 1534,56

160 1534,56

160 754,90

160 1534,56

160 1534,56

I'm trying in any way (RANGESUM, ABOVE etc.), but I can't reach my target.

I would have my sum as 3824,02 (1534,56+1534,56+754,90) instead of total sum 6893,14.

Please help me. Thank you.

[#2]

you should use something to define the aggregation mode of the information you want to sum up.

Note: if you are interested there is an Italian forum here in the community where you can post your request and directly in italian.

[#3]

I know, I need some kind of aggregation, but this is my problem....I can't find a right aggregation that is able to produce the right result.

[#4]

Looking at your example, which is the criterion you use to sum the first three elements and then the last two?

[#5]

Simply I have a maximum of values to sum (for example 3) for each type of DRG (in the example it's only one type n.160, but I can have different DRG's and I should sum first 3 for each kind), so I must have a partial sum of first 3 values, and "avoid" all the last 2 (or 3, 4.....every last values of the table).

Maybe only to know "where" I'm in the table (how many values I've yet summed, or how many rows I've yet "examinated") could be an help. Infact, I stop summing when I've summed the first 3 TARIFFA values in the first 3 rows.

I hope I was able to explain it in a right way.

Thanx for helping me.

P.S.

I can't find Italian Forum. Thank you.

[#6]

for the italian group go here: http://community.qlik.com/groups/

then look for

KILQ ITALIA

[#7]

I think your issue is that you have no unique key in your raw data, so a QV table would not know how to seperate and order the records, and thus be able to pick "the first three". By adding a unique key to the data you can now pick the first three and sum them using rangesum with above. Please look at the attached example.

[#8]

Eugene your solution sums always the last three recods while passing all the data way down. Unfortunately the example provided is tricky as the numbers, apparently, come to a correct total.

If you change the numbers in the inline table you can see that the script sums the three numbers just above the current position.

eg.

Table1:
Load * Inline
[
ID, TARIFFA
160, 10
160, 20
160, 30
160, 40
160, 50
170, 100
170, 200
170, 300
170, 400
170, 500
];

the special sum provide the following results:

10 as 10
30 as 10 +20
60 as 10 + 20 + 30
then
90 as 20 + 30 + 40
and so on.

I have produced the following script, even if, in my opinion the approach to this kind of problem is wrong starting in the design phase.

Basically, starting from the good point of Eugene, I have created a flag to identify the first 3 values of each group of IDs.

I'm sure there will be more elegant and easy approach, but this should be a starting point.

[#9]

Perhaps this is closer to what you need:

rangesum

(top( TARIFFA,1,3))

[#10]

mmhh easier than thought.

I think this is the right solution