Working out count differences between report dates

Q&A
,
1

I have a new data source which is basically a log. Every day it records data, and pushes this data into this source. I have report_date field which is the date the data was recorded. I have applicantid which is the field which stores the customers case ID. What i want to do is do a difference in distinct_count of applicantid between yesterday and the day before (rolling), providing me with the amount of new cases ‘yesterday’.

So the logic i have is:
get yesterdays distinct count of applicantID
get the day befores distinct count of applicantID
newfield = daybeforedistinctcount - yesterdaysdistinctcount

these would also be a rolling date to make sure it always equals yesterday and day before (if that makes sense).

When i try to implement this logic, i get stuck as struggle to return the right values.

Any help is appreciated

2

Hi @HarveyB-B,
I would suggest doing this all in one calculated field to save you some additional work.
It could look something like this:
distinctCountOver(ifelse(truncDate('DD', {date}) = truncDate('DD', addDateTime(-2, 'DD', now())), {applicantID}, NULL), []emphasized text, PRE_AGG)

Additionally, you could also achieve using relative date parameters. Check out this video (covered in the first couple minutes) if you’d like more information on building them!

1 Like