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

Pidgeo
Pidgeo ⚪️

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 Portland, Oregon 🥷

    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.

    **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 Budapest / Portland, OR 🟤

    @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 ⚪️

    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