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

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.

MarkSnodgrass

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.

jaeW_at_Onyx

@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)

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