Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculating Min Number per Date

Hello,

I am trying to calculate the total amount of players per month who did the First Time Deposit (FTD). The resident tables are the following

TRAN1:

[PL_PLAYER_ID, PL_TRANS_DATE, PL_OPER, PL_AMT

'A' , 20170906, 'C', 100,

'B', 20170907,'C',50,

'C',20171006,'C', 10];

PLA1:

[PL_PLAYER_ID, PL_REG_DATE, PL_UPD_DATE,

'A', 20170906, 20171007,

'B', 20170906, 20171007,

'C', 20170907, 201710107];

The Time aggregation table is build as follows:

DateBridge:

Load Distinct PL_PLAYER_ID, PL_REG_DATE as TP_GLB_DATE, PL_REG_DATE

Resident [PLA1];

Outer Join

Load Distinct PL_PLAYER_ID, PL_TRANS_DATE as TP_GLB_DATE, PL_TRANS_DATE

Resident [TRAN1];

I'm using the following measure to count the #players with First Deposit Date per month as follows:

PLAYER_FTD ==> count( aggr(Min({$<PL_OPER={'C'}>}TP_TRANS_DATE), PL_PLAYER_ID))

but when i see in a Table widget, i'm getting the following:

Month (Left(TP_GLD_DATE,6))        PLAYER_FTD

-------------------------------------------      -------------------------------------------

201709                                                3

201710                                                0

and i was expecting the following:

Month (Left(TP_GLD_DATE,6))        PLAYER_FTD

-------------------------------------------      -------------------------------------------

201709                                                2

201710                                                1

I don't know what i'm doing wrong. Can anyone help me out ?

Thanks in Advance,

Francisco

2 Replies
sunny_talwar

May be try this

Sum(Aggr(If(Min(TOTAL <PL_PLAYER_ID>{$<PL_OPER={'C'}>}PL_TRANS_DATE) = PL_TRANS_DATE, 1, 0), PL_PLAYER_ID, PL_TRANS_DATE))


Capture.PNG

Anonymous
Not applicable
Author

Many thanks, it worked perfectly....