Distinct Count of Donations and Distinct Count of Donors with those Distinct Count of Donations

Jbrorby
Jbrorby Contributor
in Beast Mode

Essentially we would want the distinct count of donations to by the buckets and the distinct count of donors to by the Y axis. But we want to be able to do it dynamically so that we can adjust date ranges on the card level to whatever we want. So I don't know that I could do this in ETL without pushing it through each possible date range they may want to filter by. Does anyone know if this is possible to achieve at a card level? I was trying to think of a way to do it using FIXED BY and variable functions at the card level in beastmodes.

Also, the reason we use distinct counts is because a single unique donation id could have many rows due to other factors and the same with donor ids.

Here is an example, we basically want to take A1:D18 and turn it into G4:H14 and to have that update when the date range (collection date) is adjusted.

image.png

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    edited September 2023 Answer ✓

    I've gotten close, but I can't get 100% there.

    I am using a count of the donation_id field and then this beastmode:

    (COUNT(DISTINCT donor_id) OVER (PARTITION BY COUNT(donation_id)))

    I can't seem to figure out why that extra row is there at the bottom of the table:

    Screen Shot 2023-09-05 at 9.55.03 PM.png

    I am guessing it has to do with the fact that there are 4 unique donor_id's and a row is being created for all 4.

    So close, but not quite there.

    If I solved your problem, please select "yes" above

Answers

  • Jbrorby
    Jbrorby Contributor

    Actually, this would be a better visual example

    image.png
  • ColemenWilson
    ColemenWilson Coach
    edited September 2023

    You would create 2 beastmodes and then bring them into your table card:

    1. COUNT(DISTINCT donor_id)
    2. COUNT(DISTINCT donation_id)

    If I solved your problem, please select "yes" above

  • Jbrorby
    Jbrorby Contributor

    If I do that, I get this:

    image.png

    I feel like there would maybe need to be some grouping

  • ColemenWilson
    ColemenWilson Coach
    edited September 2023

    What are you trying to group by? It is not clear in your screenshot example. Is it donation procedure? If so just add that field to the table card next to your 2 beastmode fields.

    If I solved your problem, please select "yes" above

  • Jbrorby
    Jbrorby Contributor

    It would be the distinct count of donation ids grouped by [date range], [acceptable procedures], then the count of distinct donors ids grouped by the above donation id counts.

    So for example, if I did this in ETL, I would filter donations to the appropriate date range and acceptable donation procedures, I would put that in a Group By tile and do distinct count donation ids grouped by unique donor ids. Then I would put that in a Group By tile and group by distinct count of donation ids created in the first group by and do a distinct count donor.

    So if 5 donors donated 7 times, and 3 donors donated 4 times, the X axis would have 7 and 4 and the Y would have 5 and 3. But the problem is that I want to be able to adjust the date range to anything.

  • ColemenWilson
    ColemenWilson Coach
    edited September 2023 Answer ✓

    I've gotten close, but I can't get 100% there.

    I am using a count of the donation_id field and then this beastmode:

    (COUNT(DISTINCT donor_id) OVER (PARTITION BY COUNT(donation_id)))

    I can't seem to figure out why that extra row is there at the bottom of the table:

    Screen Shot 2023-09-05 at 9.55.03 PM.png

    I am guessing it has to do with the fact that there are 4 unique donor_id's and a row is being created for all 4.

    So close, but not quite there.

    If I solved your problem, please select "yes" above

  • ColemenWilson
    ColemenWilson Coach

    One other approach would be to have a few common, preselected date ranges that will be used and then you can use Magic ETL to produce the groupings based on those date ranges. Users wouldn't be able to select any date range, but they'd at least have some options.

    If I solved your problem, please select "yes" above

  • Jbrorby
    Jbrorby Contributor

    Yes, I think that might be the route we take. I didn't think this would be possible at the card level, but I was not sure so I thought I'd reach out to the Dojo. Thanks

Categories