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

How to calculate sum(Points) by Week and User

daniportero6 — Sat, 16 Nov 2024 04:59:35 GMT

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 🙂

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

swuehl — Tue, 11 Dec 2018 16:38:51 GMT

Try something like

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

in a Chart with dimensions User and Week.

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

dplr-rn — Tue, 11 Dec 2018 17:27:50 GMT

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;

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

daniportero6 — Wed, 12 Dec 2018 08:58:55 GMT

Thank you Swehl,

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

Regards!

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

daniportero6 — Wed, 12 Dec 2018 09:00:09 GMT

I've never done that kind of script.

It's a nice one!

Thank you Dilipranjith 🙂

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

daniportero6 — Wed, 12 Dec 2018 15:42:59 GMT

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 🙂

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

swuehl — Wed, 12 Dec 2018 16:27:57 GMT

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?

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

daniportero6 — Thu, 13 Dec 2018 14:56:28 GMT

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

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

swuehl — Thu, 13 Dec 2018 20:39:37 GMT

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

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

daniportero6 — Fri, 14 Dec 2018 10:07:12 GMT

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 🙂

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

daniportero6 — Fri, 14 Dec 2018 10:12:04 GMT

Here is the new change, I deleted JornadaLiga, and now I use Jornada in Ranking table, but I need calculate the global position like before.