Discussion board where members can get started with QlikView.
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)
Solved! Go to Solution.
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;
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:
Jornada as JornadaLiga,
IterNo() as 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.
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?
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.
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).
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)?