Count for Aggregate SUM

[Anonymous]

Hi,

I have a Table with the following information

T1:

Load

Inline

[PL_PLAYER_ID, PL_OPER_TYPE, PL_NR_BALANCE_MOV, DATE

10, 'C', 10, 20171009,

20, 'C', 20, 20171009,

10,'C',15, 20171009,

15, 'D',10, 20171010

12,'C',10,20171110];

I'm trying to create a Dimension CATEGORY which will give me:

= IF( aggr(SUM({$<PL_OPER_TYPE={'C'}>}PL_NR_BALANCE_MOV), [PL_PLAYER_ID] )=0, 'A',

  IF( aggr(SUM({$<PL_OPER_TYPE={'C'}>}PL_NR_BALANCE_MOV), [PL_PLAYER_ID] )<20, 'B','C'))

If SUM of PL_OPER_TYPE=0 -- 'A'

If SUM of PL_OPER_TYPE<20 -- 'B'

If SUM of PL_OPER_TYPE>=20 -- 'C'

After i'm planning to have a Measure which will count the number of PL_PLAYER_ID in each CATEGORY

Measure ==> count(distinct PL_PLAYER_ID)

However when i combine the dimension and the measure gives me strange values and not something like:

CATEGORY          MEASURE

A                     --> 1

B                     -->  1

C                    --> 2

Can anyone help me in what am i doing wrong ?

Thanks in Advance

[sunny_talwar]

Try this

Dimension

=Aggr(

If(Sum({$<PL_OPER_TYPE={'C'}>}PL_NR_BALANCE_MOV) + Sum({1} 0) = 0, 'A',

If(Sum({$<PL_OPER_TYPE={'C'}>}PL_NR_BALANCE_MOV) < 20, 'B', 'C')), PL_PLAYER_ID)

Expression

=Count(DISTINCT PL_PLAYER_ID)

[Anonymous]

It worked perfectly. Many thanks Sunny.

[sunny_talwar]

I am glad it worked

[shraddha_g]

Please mark correct answer.. It will help other members.

Qlik Community Tip: Marking Replies as Correct or Helpful