Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
pls try:
Count(
{$<REG_DATE={"$(<=(REG_DATE)-30)"}>}
aggr(TRANSID,CUSTOMER))
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))
Okay I have tried and the result says everything is 0.
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.
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 |
create a key of customer & transid. then base your aggr on that.
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.
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.
I do not have 'result' in my original data. That is the expected outcome line for each data. 😥