6 Replies Latest reply: Dec 20, 2017 6:45 PM by Francisco Pedrosa RSS

    Sum Values after a Specific Date

    Francisco Pedrosa

      Hi,

       

      I have two resident tables with the following information:

       

      T1:

      Load inline

      [PL_PLAYER_ID, PL_TIME_HOUR, GM_NR_BETS,

      'A', 20171009, 10,

      'B', 20171009, 20,

      'A', 20171020, 25,

      'A', 20171021, 10];

       

      T2;

      Load inline

      [PL_PLAYER_ID, PL_TIME_HOUR, PL_OPER_TYPE, PL_VALUE,

      'A', 20171005,'C',1000,

      'B', 20171008,'C',500,

      'A',20171012,'D',100,

      'A',20171018,'D',150,

      'B',20171019,'D',10]

       

      I'm tring to build a measure which will give the sum of GM_NR_BETS after MAX(PL_TIME_HOUR) when PL_OPER_TYPE='D' for a specific PL_PLAYER_ID.

       

      I'm using the following, but it always return me 0

      SUM(IF(TP_TIME_HOUR > aggr(MAX({$<PL_OPER_TYPE={'DEBITO'}>}TP_TIME_HOUR),PL_PLAYER_ID), GM_NR_BETS, 0))

       

      i was expecting the following:

      For PL_PLAYER_ID='A' ==> [sum of GM_NR_BETS after MAX(PL_TIME_HOUR) when PL_OPER_TYPE='D'] = 35

      For PL_PLAYER_ID='B' ==> [sum of GM_NR_BETS after MAX(PL_TIME_HOUR) when PL_OPER_TYPE='D'] = 0


      Can anyone help with this Measure building, please ?


      Thanks in advance.


      Francisco