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

Abby Brinks
Abby Brinks Member
edited November 2023 in Magic ETL

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?

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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:

    image.png

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Abby Brinks
    Abby Brinks Member
    edited November 2023

    Thank you! I need to have each call broken out and I believe I've figured out how to do that by doing another rank and window off another branch of the input without a grouping and to do a row number function, orderying by timestamp, and partitioning by ANI and date. I'm then joining the other grouped rank and window back onto the ungrouped rank and window (ungrouped on the left), then doing a function where I subtract the number producted by the grouped rank and window by the row number -1. That way, if someone calls a 4th, 5th, and 6th time on a 2nd day, it shows up accordingly for each of those calls, rather than returning a 6 for each of those calls.

    I'm having an issue though in the grouped rank and window where if someone calls on August 1, not at all on August 2, and then again on August 3, it's counting August 1 as the previous "day" because there's no row for August 2. Is there a way to acccount for that?

    Thank you!

  • Abby Brinks
    Abby Brinks Member
    image.png

    Above is what I have built.

    image.png

    I put an offshoot "checkpoint" off the top rank and window, and this is what I'm seeing for one of the ANIs. For example, in the "two day range", I'm expecting to see 3 in the second row, because August 1 is outside of the 2 day range, but it's considered the previous row in the rank and window. This is what I'm trying to correct.

Categories