5 Replies Latest reply: Dec 12, 2017 6:10 PM by Luis Madriz 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

       

        • Re: Sum Values after a Specific Date
          Luis Madriz

          Hola Francisco,

           

          Please help me understand your requirement: PL_TIME_HOUR appears on both tables so they are associated and you want the bets after the max of PL_TIME_HOUR? As the field is the same there will be nothing after the max and everything before the max


          Can you please confirm?


          Cheers,


          Luis

          • Re: Sum Values after a Specific Date
            kaan erisen

            Hi,

             

            I assume PL_TIME_HOUR fields are actually different on both table. Otherwise there is no match between two table. I guess the date field on T2 is a referance date.

             

             

            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_REF, 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]

             

             

            Measure : SUM(AGGR(SUM({<PL_TIME_HOUR={">$(=MAX(PL_TIME_HOUR_REF))"},PL_OPER_TYPE={'D'}>}GM_NR_BETS),PL_PLAYER_ID))

             

            Untitled.png

             

            If not showing PL_PLAYER_ID when Total GM_NR_BET is zero not a problem then you can remove the aggr part of the measure

             

            SUM({<PL_TIME_HOUR={">$(=MAX(PL_TIME_HOUR_REF))"},PL_OPER_TYPE={'D'}>}GM_NR_BETS)