topic Re: How to create a table with sum(Points) by User and Week in QlikView

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

daniportero6 — Thu, 06 Sep 2018 10:43:08 GMT

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?

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

marcus_sommer — Fri, 07 Sep 2018 07:40:07 GMT

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

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

MarcoWedel — Fri, 07 Sep 2018 21:55:04 GMT

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

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

daniportero6 — Mon, 10 Sep 2018 12:08:45 GMT

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

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

MarcoWedel — Mon, 10 Sep 2018 21:00:15 GMT

Hi,

maybe like this?

hope this helps

Marco

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

daniportero6 — Tue, 11 Sep 2018 07:49:37 GMT

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

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

MarcoWedel — Tue, 11 Sep 2018 21:24:09 GMT

here you go:

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

hope this helps

Marco

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

daniportero6 — Wed, 12 Sep 2018 08:25:03 GMT

Oh God! I would never imagine that solution haha

I really appreciate it Thank you

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

MarcoWedel — Wed, 12 Sep 2018 21:11:58 GMT

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

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

daniportero6 — Tue, 18 Sep 2018 08:22:03 GMT

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

Any help?

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

MarcoWedel — Tue, 18 Sep 2018 18:34:48 GMT

Hi again,

some screenshots that should help you configure your charts:

Num(-Rank(Sum({$<User>} Points)),'0°;0°')

regards

Marco

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

daniportero6 — Thu, 20 Sep 2018 07:32:25 GMT

Thanks you so much Marco!

Regards