4 Replies Latest reply: Dec 5, 2017 11:08 PM by Shraddha Gajare RSS

    Count for Aggregate SUM

    Francisco Pedrosa

      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