How to create a table with sum(Points) by User and Week

[daniportero6]

Hi everyone,

I need some help with a problem that I have with a table on Qlikview.

The problem is that I wanna show a graphic like this:

I have the next table:

Week, User, Points: [

1, A, 15

1, B, 7

1, C, 10

2, A, 2

2, B, 20,

2, C, 9

...

Untill week 38]

Then, I wanna show the evolution of the table (1º, 2º, 3º) by Week and user like the pic from above.

Should be with sum(Points):

      week 1: { A-15, B-7, C-10}, so 1º-A, 2º-C, 3º-B

      week 2: { A-17, B-27, C-19}, so 1º-B, 2º-C, 3º-A

I tryed with RangeSum(sum(Points), 0, Week)) but it did not work.

Any help please?

[marcus_sommer]

I'm not quite sure if I understand what do you want to do but you missed the interrecord-function to cumulate the data. Therefore try it with: RangeSum(above(sum(Points), 0, rowno(total)))

- Marcus

[MarcoWedel · Accepted solution]

So you are looking for something like the rank of each fully accumulated points over weeks per user?

maybe like this:

table1:
LOAD * INLINE [
    Week, User, Points
    1, A, 15
    1, B, 7
    1, C, 10
    2, A, 2
    2, B, 20
    2, C, 9
];

LOAD RecNo()+2 as Week,
     Chr(64+IterNo()) as User,
     Ceil(Rand()*20) as Points
AutoGenerate 36
While IterNo()<=3;

tabAsOfWeek:
LOAD Distinct
     Week as AsOfWeek,
     IterNo() as Week
Resident table1
While IterNo()<=Week;

hope this helps

regards

Marco

[daniportero6]

It helped me a lot!

Now I'm trying to order by ranking. I mean, now it's from up to bottom: 14, 13, 12... 1.

And I want to see from up to bottom: 1, 2, 3... 14, but I don't find anything to change the order. Do you know any tip?

Thanks for the answer

Daniel

[MarcoWedel]

Hi,

maybe like this?

hope this helps

Marco

[daniportero6]

Yes! That's what I mean.

Sorry, but I can not open the file because I just have Persional Edition version, so I just can open and use files from my laptop.

Could you tell me how did you do it?

Thank you!

Daniel

[MarcoWedel]

here you go:

Num(-Rank(Sum(Points)),'0;0')

hope this helps

Marco

[daniportero6]

Oh God! I would never imagine that solution haha

I really appreciate it   Thank you

[MarcoWedel]

glad you like it.

finally here's another one with slightly altered expression axis number format and some colour brushing options to emphasize selected users.

background color expression:

If(GetSelectedCount(User),If(Count(User),If(GetSelectedCount(User)=1,LightGreen()),LightGray()))

line style expression:

If(GetSelectedCount(User),If(Count(User),'<W3>','<S2>'))

hope this helps

regards

Marco

[daniportero6]

Yeah! I would like to do that, but I don't know where I could write it,I can't find it

Any help?