# New to QlikView

Discussion board where members can get started with QlikView.

New Contributor III

## How to calculate sum(Points) by Week and User

Hi!

I got a question, I've tryed to create a table with sum(points) by week and user. The example is like this:

User       Week        Points       Sum(Points)

Juan        1                   12                   12
Luis          1                   20                   20
Dani         1                   8                      8
Juan        2                   10                   22 (12+10)
Luis          2                   7                     27 (20+7)
Dani         2                   11                  19 (8+11)

Any help?

Thank you 🙂

Labels (3)

• ### sum

1 Solution

Accepted Solutions
MVP

## Re: How to calculate sum(Points) by Week and User

Try something like

=Sum(Aggr( Rangesum(Above(Sum(Points),0,Rowno()), User, Week)

in a Chart with dimensions User and Week.

10 Replies
MVP

## Re: How to calculate sum(Points) by Week and User

Try something like

=Sum(Aggr( Rangesum(Above(Sum(Points),0,Rowno()), User, Week)

in a Chart with dimensions User and Week.

Partner

## Re: How to calculate sum(Points) by Week and User

another option to swuehl's
do it at script level

```UserTemp:
Week,
Points
FROM
(ooxml, embedded labels, table is Sheet1);

UserFact:
Week,
Points,
if(User=Previous(User),Previous(PointsAccum)+Points,Points) as PointsAccum;
User,
Week,
Points,
Points as PointsAccum
resident UserTemp
order by User, Week;

drop table UserTemp;```

New Contributor III

## Re: How to calculate sum(Points) by Week and User

Thank you Swehl,

I did something like that but I missed the Above().

Regards!

New Contributor III

## Re: How to calculate sum(Points) by Week and User

I've never done that kind of script.

It's a nice one!

Thank you Dilipranjith 🙂

New Contributor III

## Re: How to calculate sum(Points) by Week and User

Sorry, but I realize it's not that easy.

I post attached my diagram model.

I wanna calculate sum(puntos) by usuario and JORNADALIGA, not Jornada, which is in a different table.

Ranking has this script:

Resident Puntos

So, my problem is that I tryed with a left join (Puntos), but it's does not work.

Jornada has 38 values, and JornadaLiga has 15, but every week has one more, till complete the 38 weeks of the League.

If need more details, let me know please.

Thank you 🙂

MVP

## Re: How to calculate sum(Points) by Week and User

Sorry, I am not sure how your last post relates to your original question or what you are trying to do.

Could you post a small sample QVW with some sample data?

New Contributor III

## Re: How to calculate sum(Points) by Week and User

Sure!

I'm not sure if you can open it, I never can open the QVW file from anyone, but hope you can 🙂

In case you can not open it, let me know and I'll post the full script.

Regards

Highlighted
MVP

## Re: How to calculate sum(Points) by Week and User

Yes, I can open the qvw.

The reason for JornadaLiga having 15 values and Jornada 38 is that you are using a RESIDENT LOAD of table Puntos to create your Ranking, and the field Jornada only shows these 15 values. Jornada is a key field, the complete set of all values can be found in your Calendario table.

I am not sure what you are trying to achieve, but if you need the 38 values in your Ranking table, then use the Calendario table to get all available Jornada values.

It seems like your ranking is kind of an As-of-table .

Is this what you want to do? This could be a script based solution for your full accumulation.

Then maybe create a chart with dimensions Usuario and JornadaLiga and as expression just =Sum(Puntos).

New Contributor III

## Re: How to calculate sum(Points) by Week and User

What I would like to achieve is just show in Ranking table the weeks which have been played, not all of them. And then, I can calculate how change the positions of every week. But to show just the week have been played, I did that script to calculate JornadaLiga.

Is there any way to show the same table Ranking using Jornada and not JornadaLiga, but calculating the global position in the ranking for every player in that week, not sum(Puntos)?

Regards 🙂