2 Replies Latest reply: Jan 5, 2018 9:51 AM by Francisco Pedrosa RSS

    Calculating Min Number per Date

    Francisco Pedrosa

      Hello,

       

      I am trying to calculate the total amount of players per month who did the First Time Deposit (FTD). The resident tables are the following

       

      TRAN1:

      [PL_PLAYER_ID, PL_TRANS_DATE, PL_OPER, PL_AMT

      'A' , 20170906, 'C', 100,

      'B', 20170907,'C',50,

      'C',20171006,'C', 10];

       

      PLA1:

      [PL_PLAYER_ID, PL_REG_DATE, PL_UPD_DATE,

      'A', 20170906, 20171007,

      'B', 20170906, 20171007,

      'C', 20170907, 201710107];

       

      The Time aggregation table is build as follows:

      DateBridge:

      Load Distinct PL_PLAYER_ID, PL_REG_DATE as TP_GLB_DATE, PL_REG_DATE

      Resident [PLA1];

      Outer Join

      Load Distinct PL_PLAYER_ID, PL_TRANS_DATE as TP_GLB_DATE, PL_TRANS_DATE

      Resident [TRAN1];

       

      I'm using the following measure to count the #players with First Deposit Date per month as follows:

      PLAYER_FTD ==> count( aggr(Min({$<PL_OPER={'C'}>}TP_TRANS_DATE), PL_PLAYER_ID))

       

      but when i see in a Table widget, i'm getting the following:

       

      Month (Left(TP_GLD_DATE,6))        PLAYER_FTD

      -------------------------------------------      -------------------------------------------

      201709                                                3

      201710                                                0

       

      and i was expecting the following:

      Month (Left(TP_GLD_DATE,6))        PLAYER_FTD

      -------------------------------------------      -------------------------------------------

      201709                                                2

      201710                                                1

       

      I don't know what i'm doing wrong. Can anyone help me out ?

       

      Thanks in Advance,

       

      Francisco