topic Partial sum in a table in QlikView

Partial sum in a table

Tue, 19 May 2009 17:52:45 GMT

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 754,90

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.

Partial sum in a table

Tue, 19 May 2009 17:57:08 GMT

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.

Partial sum in a table

Wed, 20 May 2009 15:35:48 GMT

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.

Partial sum in a table

Wed, 20 May 2009 16:54:19 GMT

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

Partial sum in a table

Wed, 20 May 2009 17:10:32 GMT

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.

Partial sum in a table

Wed, 20 May 2009 17:22:19 GMT

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

then look for

KILQ ITALIA

Partial sum in a table

Thu, 21 May 2009 00:27:04 GMT

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.

Partial sum in a table

Thu, 21 May 2009 04:45:21 GMT

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.

Partial sum in a table

Fri, 22 May 2009 01:18:53 GMT

Perhaps this is closer to what you need:

rangesum

(top( TARIFFA,1,3))

Partial sum in a table

Fri, 22 May 2009 01:30:21 GMT

mmhh easier than thought.

I think this is the right solution

Partial sum in a table

Wed, 27 May 2009 23:10:34 GMT

Thnx to all, the solution posted by Eugene ia able to sum first 3 values......but the table data to analyze is more complicated and the number of values that I have to sum changes every time in reason of the ID number.

So in the script the number three shoudn't be a constant but a variable calculated dynamically for any kind of ID.

Maybe a faster solution is to number every record from 1 to n for every kind of ID:

160 1

160 2

160 3

135 1

135 2

120 1

120 2

160 4

120 3

etc . , so I could sum the first X values for every kind of ID.

I hope I was able to explain it clearly.

Partial sum in a table

Thu, 28 May 2009 00:03:29 GMT

Is there a forumla that calculates the count of records to sum based on ID, or is it more like a different number for each ID, with no pattern? In the second case the solution would be a seperate mapping table. Let me know, and I can show you how I would do it.

Partial sum in a table

Fri, 29 May 2009 15:18:58 GMT

I have a formula like this as an expression of my graph table:

Count( IF( CONDITION, ID) )

that count how many IDs are admissible (for each kind of ID) so this is equal to maxiumu number of ID admitted to calculate sum, even if in the table I have more IDs.

So for every kind of ID i have a limit in summing values.

Partial sum in a table

Mon, 01 Jun 2009 21:00:06 GMT

I would use a formula like the one you included in your post, not in the dimension, but instead inside an aggr function which would be the third argument to this "top" function:

rangesum

(top( TARIFFA,1,3))

The dimension for the aggr function would likely be ID. If you need more help attach your qvw with some notes.