Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all, i have a data where i have customer ID against dates. In same table i have "status" field where it tells customer is either active etc. Issue i am trying to solve is, how can i do a count on unique customers in a day and make a report where i see increment or decrease in number of customers seen over time? DISTINCT doesnt work due to "status" field since customer may be active on one day and seen deactive on another day
Can you post a sample data and the expected output.
Do you want the count of 'Active' customers on a particular date ?
count(Distinct{<Status={'Active'}>} Customer ID)
Sample data
DATE | CUST_ID | STATUS |
1/06/2016 | 201 | CREATE |
1/06/2016 | 201 | ACTIVE |
1/06/2016 | 201 | SYNCED |
1/06/2016 | 203 | ACTIVE |
1/06/2016 | 204 | CREATE |
1/06/2016 | 206 | ACTIVE |
1/06/2016 | 210 | ACTIVE |
1/06/2016 | 227 | CREATE |
2/06/2016 | 201 | ACTIVE |
2/06/2016 | 210 | ACTIVE |
2/06/2016 | 204 | ACTIVE |
2/06/2016 | 230 | CREATE |
as you can see there were 6 distinct users on 01st of June, and second of june there were 4, however on 2nd of June, there was one new user" 230" so i am hoping to get output like this
1st of June user count = 6
2nd of June , User count = 7 (6+1)
hope it makes sense? THanks in advance
Check Stefan's response here: Accumulated Distinct Count