Sum values based on a Date

[Anonymous]

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

[sunny_talwar]

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

[jerryyang756]

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

[Anonymous]

Hi Pritam,

Thanks for the support.

These are huge tables that unfortunately have much more information and i can't drop it. I understood the rational presented.

Do you think that i have a solution keeping up the tables GAMES and PLAYER ?

Thanks in advance

Francisco

[Anonymous]

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