Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
daniportero6
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 Smiley Happy

Labels (3)
1 Solution

Accepted Solutions
MVP
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
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.

dilipranjith
Valued Contributor III

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

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
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!

 
daniportero6
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 Smiley Happy

daniportero6
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:

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 Smiley Happy

Highlighted
MVP
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?

daniportero6
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 Smiley Happy

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

Regards

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

 

2018-12-13 21_37_09-Window.png

 

daniportero6
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 Smiley Happy

Community Browser