Beast mode help - a count of users who transact once a month in each of the last three months

Pidgeo
Pidgeo Member
in Magic ETL

Hi,

Any Beast Mode gurus able to help me with how they think it would be possible to return a distinct count of users (customerid) who have transacted (transactiontime) at least once a month in each of the last three months? The table has every transaction made as a single row with a time and date stamp if that helps.

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    If you set your date range to the last 3 months on your card, you could use a beast mode like this:

    COUNT(DISTINCT CONCAT(`Customerid`,'-',LAST_DAY(`transactiontime`)))

    You would then filter to where the count equals 3.

    **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.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @MarkSnodgrass i love that! but it'd only work if you kept customer_id on the axis. so you'd see each customer and then be able to filter or see if they have the count = 3. but if you just wanted one number it wouldn't work.

    at this point it may be easiest to make a summary dataset with the granularity of one row per user per month and a binary flag if they transacted, or total transactions (could create this as a dataset view). from there your count distinct calc and accompanying viz would be trivial (CASE statement or filter as Mark suggested)

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Pidgeo
    Pidgeo Member

    Thanks @MarkSnodgrass and @jaeW_at_Onyx for both suggestions! Will work with our tech team to see if we can get it to work as suggested (as they have access to build datasets and I don't) but from my limited knowledge it makes sense in my head

Categories