topic Re: Group and Count Expression in QlikView

Group and Count Expression

stevegimbrollmt — Mon, 03 Nov 2014 22:14:34 GMT

Hi guys,

I am not how I go about this, even how to title it

So let me try and explain. Take this example

YearMonth	Date	Active Players	Game A	Game B	NumberofGamesPlayed
2014-10	28/10/2014	508	484	18	502
2014-10	27/10/2014	2,011	2,067	56	2,123
2014-10	26/10/2014	2,160	2,229	47	2276
2014-10	25/10/2014	1,875	1,907	48	1955
2014-10	24/10/2014	1,821	1,817	42	1859
		8375	8504	211	8715

So if Player X plays Game Product A 10 times in a month and plays on Game Product B 10 times. The value of NumberOfGamesPlayed would be 2. not 20

And for the active number of players in a month (8375 in the example above I need to know the average of all players value for NumberOfGamesPlayed - the average number of Games Product played

How can I go about this, I have tried using AGGR to check if GameA>0 than 1 and than try and Sum that but its not working

Re: Group and Count Expression

maxgro — Mon, 03 Nov 2014 22:36:16 GMT

I suppose your source data is

Player, YearMonth, Date, Game A, Game B

with YearMonth as dimension

expression maybe

sum(aggr(rangesum(if(SUM([Game A])>0,1), if(sum([Game B])>0,1)), YearMonth, Player))

Re: Group and Count Expression

stevegimbrollmt — Mon, 03 Nov 2014 23:50:40 GMT

Hi Massimo Thanks a lot

That is helpful and I think I am on the correct track, I am at the moment trying it out.

Basically from that table

I would end up having like

YearMonth Date Game A Game B

2014-10 8375 1000 500

Because lets say Player 1 who played 30 times in a month, it would count as 1 for GAME A, and maybe another 1 for Game B so in all it would be 2 ,

Re: Group and Count Expression

stevegimbrollmt — Mon, 10 Nov 2014 22:41:02 GMT

Massimo

Just wanted to confirm that what I have asked for, and what you answered is very very close to what is needed, I say almost, because it does exactly what it needs, I just need to think of extra logic for the next step.

Thank you. mission accomplished on this one.

Steve