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

    Sum values based on a Date

    Francisco Pedrosa

      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


        • Re: Sum values based on a Date
          Sunny Talwar

          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

              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

              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