Trying to Count Instances within certain date range where 1 field is the same

Abby Brinks

Hello!

I am trying to replicate an excel formula to determine the number of times a caller calls within 4 days, 3 days, 2 days, and 1 day. I would like to create a field for each of these time windows. If someone calls 6 times within 4 days, I would like the 6th call to have the number 5 in it in the "4 day window" flag.

The excel formula I am trying to replicate says this:

=COUNTIFS($D$1:D10,$D10,$A$1:A10,"<="&A10,$A$1:A10,">"&INT(A10)-4)

where D is the ANI and A is the date. The -4 at the end is for the 4 day time window field, and that number changes for each time window field. I selected row 10 as an example. In each row, the formula is selecting the current row and the rows above it.

This will be determined by the field 'ANI' (essentially phone number), and 'Date', to determine the date range. Each row signifies one phone call. I do also have timestamp as a field as well. I have tried to achieve this in Magic ETL by utilizing rank and window, MySQL (the version Domo uses does not support 'Partition' and 'Over'), and beastmode, to no avail. Any ideas on how to achieve this?

MarkSnodgrass

You should be able to do this by grouping your data by caller and date, then joining to the Domo Dimensions Calendar dataset and then using the rank and window to create a 3 day, 4 day, etc. total. It would look something like this:

You would replace my covid sample dataset with your caller dataset. In the group by tile, group by caller and date and count the number of calls. This will give you the total number of calls by each caller on each date they called.
Add the Domo Calendar dataset. I would recommend filtering this dataset to a reasonable date range as it goes back to the 1980's and also goes into the future.
Add a Join tile and put the calendar on the left side and join on the two date columns.
In the Rank & Window, create a 3 day total by choosing Sum and your call count field and then preceding 1 and following 2. Order by the date field that is from the calendar dataset and partition by your caller. Create 4 day and 5 day totals by doing the same as the 3 day, but extending your following number.

This should get you what you want.