How to count the transaction happened in the past 30 days from the given date.

[Qlik496]

Hello I am new to Qlik.

I want to count the transaction happened in the past 30 days from the given day for each customer.

So I tried to use this code in the sheet.

Aggr(count(distinct if( NUM(Date(REG_DATE,'YYYY-MM-DD')-30) <=REG_DATE <=NUM(Date(REG_DATE,'YYYY-MM-DD'))),TRANSID),CUSTOMER)

So this code aims to get the count of distinct transaction id (TRANSID) for every customer (CUSTOMER) for the past 30 days  from the given transaction date,(REG_DATE) 

Every data has the given transaction date as I am using the transaction data. 

Help me please.

[G3S]

pls try:

Count(

{$<REG_DATE={"$(<=(REG_DATE)-30)"}>}

aggr(TRANSID,CUSTOMER))

[Qlik496]

Okay I tried but still does not work.

Can you let me know what is wrong with my code? 

Count(distinct({$<{Date(REG_DATE,'YYYY-MM-DD')-30}<=REG_DATE<={Date(REG_DATE,'YYYY-MM-DD')}>} TRANSID))

[Qlik496]

Okay I have tried and the result says everything is 0. 

[G3S]

are you able to pls share a sample set of your data and a mockup of expected results for that specific set? 

re your expression: set analysis has a specific syntax  to be followed + there's two "<=" which won't work. 

I just noticed your formula has as <=REG DATE. in the one I shared please swap  <=  with >=

this will swap the formula around. 

if REGDATE field is already in date format , it is not required to be wrapped in "date" function.

[Qlik496]

Okay, I made a sample data. The result column is what I want as a result. 

Still your code is not working for me... I tried to swap <=  with >=.

Okay so here why Result for REG_DATE = 2022-10-24 is 3 is because The Transaction 10067, at 2022-09-26 is same Transaction as 2022-10-24 one. I do not want to double count them....

CUSTOMER	TRANSID	REG_DATE	RESULT
308779	100065	2022-08-22	1
308779	100065	2022-08-22	1
308779	100067	2022-09-26	1
308779	100067	2022-09-26	1
308779	100067	2022-10-24	3
308779	100067	2022-10-24	3
308779	100067	2022-10-24	3
308779	100067	2022-10-24	3
308779	100071	2022-10-24	3
308779	100071	2022-10-24	3
308779	100072	2022-10-24	3
308779	100072	2022-10-24	3
308779	100076	2023-05-22	1
308779	100076	2023-05-22	1
308779	100077	2023-06-26	1

[G3S]

create a key of customer & transid. then base your aggr on that. 

[Qlik496]

Okay. To create a key, I guess I need to revise the Data Load Editor page.

I do not want to go deep inside... And can you help me with a example line.

Thank you so much. 

[G3S]

tried this based on the table you shared. I took 'result' as a field. key_CUST_TRANSID is simply CUSTOMER&TRANSID

if you had Count(distinct key_CUST_TRANSID) in a KPI chart it should show 6 based on the above table. 

[Qlik496]

I do not have 'result' in my original data. That is the expected outcome line for each data. 😥