Sum Values after a Specific Date

[Anonymous]

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

[luismadriz]

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

[kaanerisen]

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)

[Anonymous]

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.

[Anonymous]

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.

[luismadriz]

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

[Anonymous]

Thanks that post helped a lot and solved my problem