topic Sum values based on a Date in App Development

Sum values based on a Date

Anonymous — Mon, 05 Mar 2018 12:54:18 GMT

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 — Mon, 05 Mar 2018 13:04:34 GMT

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

jerryyang756 — Mon, 05 Mar 2018 13:11:06 GMT

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

Re: Sum values based on a Date

Anonymous — Mon, 05 Mar 2018 14:06:52 GMT

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

Re: Sum values based on a Date

Anonymous — Mon, 05 Mar 2018 14:10:15 GMT

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