How to calculate sum(Points) by Week and User

[daniportero6]

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 🙂

[swuehl · Accepted solution]

Try something like

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

in a Chart with dimensions User and Week.

[dplr-rn]

another option to swuehl's
do it at script level

UserTemp:
LOAD User, 
Week, 
Points
FROM
[..\Downloads\CommTest.xlsx]
(ooxml, embedded labels, table is Sheet1);

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

drop table UserTemp;

[daniportero6]

Thank you Swehl,

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

Regards!

[daniportero6]

I've never done that kind of script. 

It's a nice one!

Thank you Dilipranjith 🙂

[daniportero6]

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:

LOAD Distinct
      Jornada as JornadaLiga,
      IterNo() as Jornada
Resident Puntos
While IterNo()<=Jornada;

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 🙂

[swuehl]

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?

[daniportero6]

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

[swuehl]

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).

[daniportero6]

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 🙂