topic Re: Sum Values after a Specific Date in App Development

Sum Values after a Specific Date

Anonymous — Wed, 06 Dec 2017 23:35:36 GMT

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

luismadriz — Thu, 07 Dec 2017 00:22:26 GMT

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

kaanerisen — Thu, 07 Dec 2017 05:24:57 GMT

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))

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)

Re: Sum Values after a Specific Date

Anonymous — Tue, 12 Dec 2017 17:45:01 GMT

Hi Luis,

In the beggining i had different variables for the PL_TIME_HOUR and those represent when the events in table T1 and table T2 occured in time.

If i have 2 variables about time, how can i correlate both in a unique time variable ?

Thanks in advance for the support,

Cheers,

Francisco

P.S.: Sorry for the late reply, though help much appreciated.

Re: Sum Values after a Specific Date

Anonymous — Tue, 12 Dec 2017 17:46:50 GMT

Hi ,

If i have 2 representing the time when the events occurred , how can i correlate both in a unique time variable ?

Thanks in advance for the support,

Cheers,

Francisco

P.S.: Sorry for the late reply, though help much appreciated.

Re: Sum Values after a Specific Date

luismadriz — Tue, 12 Dec 2017 23:10:41 GMT

Hi,

Can you please explain the difference between the two dates?

in the meantime, please review this post as it could be helpful for this design Canonical Date

Cheers,

Luis

Re: Sum Values after a Specific Date

Anonymous — Wed, 20 Dec 2017 23:45:56 GMT

Thanks that post helped a lot and solved my problem