4 Replies Latest reply: Mar 5, 2018 9:10 AM by Francisco Pedrosa RSS

    Sum values based on a Date

    Francisco Pedrosa
    Francisco Pedrosa Mar 5, 2018 7:54 AM

      Hi,

       

      I'm trying to calculate certain amount of users based on their registration date but i'm not able to get the result. Can anyone help me on the solution ?

       

      I have two Tables:

       

      PLAYER:

      [ PLAYER_ID, REG_DATE, UPDATE_DATE,

      100,   20180103, 20180302,

      101,  20180202, 20180204,

      105, 20180204, 20180301];

       

      GAMES:

      [PLAYER_ID, GAME, TRAN_DATE,

      100, A, 20180103,

      100, A, 20180201,

      100, A, 20180202,

      101, A, 20180202,

      101, A, 20180203,

      101, A, 20180205,

      105, A, 20180204,

      105, A, 20180301,

      105, A, 20180302];


      DateBridge:

      Load Distinct PLAYER_ID, UPDATE_DATE as GLB_TIME, UPDATE_DATE

      Resident [PLAYER];

      Outer Join

      Load Distinct PL_PLAYER_ID, TRAN_DATE as GLB_TIME,  TRAN_DATE

      Resident [GAMES];

       

      I'm trying to get the # players registered per day:

       

      Date               # Players Registered

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

      20180103     1

      20180104     0

      . . .

      20180202     1

      . . .

      20180204     1

      . .  .

      20180301     0

       

      I have the following measure but it is not working:

      #Players Registered = SUM( IF( REG_DATE=GLB_TIME , 1 , 0) )

       

      Thanks in advance,

       

      Francisco


      • 67 Views
      • Tags:
        • Re: Sum values based on a Date
          Sunny Talwar
          Sunny Talwar Mar 5, 2018 8:04 AM (in response to Francisco Pedrosa)

          I am not sure I am able to follow... what is the issue that you are running into?

            • Re: Sum values based on a Date
              Francisco Pedrosa
              Francisco Pedrosa Mar 5, 2018 9:10 AM (in response to Sunny Talwar )

              Hi Sunny,

               

              Basically, i have two dates in the Player Table and the records in the Player table are "inline" with the date UPDATE_DATE with the "other Tables".

               

              These two tables PLAYER and GAMES have much more records and are linked in terms of the DATES with other Tables.

               

              I need is to know for the REG_DATE (which is not "linked" in terms of Date) with the other Tables how can i be able to account for each day of the GLB_TIME how many Players have a REG_DATE within each day ?

               

              Thanks,

               

              Francisco

            • Re: Sum values based on a Date
              Pritam Basak
              Pritam Basak Mar 5, 2018 8:11 AM (in response to Francisco Pedrosa)

              Load the script as below

               

              PLAYER:

              Load * Inline [

              PLAYER_ID, REG_DATE, UPDATE_DATE,

              100,   20180103, 20180302,

              101,  20180202, 20180204,

              105, 20180204, 20180301

              ];


              GAMES:

              LOAD * Inline [

              PLAYER_ID, GAME, TRAN_DATE,

              100, A, 20180103,

              100, A, 20180201,

              100, A, 20180202,

              101, A, 20180202,

              101, A, 20180203,

              101, A, 20180205,

              105, A, 20180204,

              105, A, 20180301,

              105, A, 20180302

              ];

              DateBridge:

              NoConcatenate

              Load Distinct PLAYER_ID, UPDATE_DATE as GLB_TIME_PLAYERS, UPDATE_DATE,REG_DATE

              Resident [PLAYER];

              Right Join

              Load Distinct PLAYER_ID, TRAN_DATE as GLB_TIME_GAMES,  TRAN_DATE

              Resident [GAMES];


              DROP Tables PLAYER,GAMES;

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

              Take a straight table

              Dimension : TRAN_DATE (Rename it to date)

              Expression: SUM( IF( REG_DATE=GLB_TIME_GAMES , 1 , 0) )


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

              Deselect the 'Supress Zero' from presentation tab

              sdf.PNG