Is it possible to calculate 6 months of data for a metric?

I am trying to collect Customer data Month over Month data for a metric that uses data from the past 6 months.

For example -

January 2022 would include customer data from July 2021 to January 2022

February 2022 would include customer data from August 2021 to February 2022

and so on..

Any help is appreciated!

Thanks!

Vik

Comments

  • vikrao
    vikrao ⚪️
    edited April 14

    This is the sql query if that helps

    Select count(distinct Customer) from CustomerData where InvoiceDate between '2021-07-01' and '2022-01-31'

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    You can do this in Magic ETL pretty easily with a formula tile, group by tile and a rank and window tile.

    In the formula tile, use the LAST_DAY() function to normalize the dates in each month to the same day, like this: LAST_DAY(datefield)

    In the group by tile, group by your last day field and do a distinct count of customer. This will give you a total for each month.

    In the rank & window tile, give your field a name like 6-month total choose sum and choose your customer count field that you created in the group by tile. Enter 5 for preceding and 0 for following.

    This will give you a 6 month total for each month, assuming you aren't missing any months.

    Hope this helps.




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • vikrao
    vikrao ⚪️

    Thanks Mark. This was very helpful.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @MarkSnodgrass i think this is logically flawed.

    if you calculate the number of distinct customers in a month, you cannot add each month's number for a year.

    i.e. 50 in jan, 35 in feb = 85 distinct customers.

    you would be assuming that you had no repeat customers...


    if your data is measured per month and you want a rolling 6 months, what you would could do is create a rolling window for each month that represents the appropriate set of dates.

    i.e on Jan 21 i need a window spanning Jan 20 to Jan 21 (so use a formula tile to calculate last_day() for Jan 21 and then last_day for the month one year ago (date_diff).

    now we need an aggregate table representing one row per month (table_a). so just GROUP BY date and MIN(date_lastYear).

    then JOIN table_a onto the full activity table, table_b. Ideally you'd want to JOIN on table_b.date between table_a.date_lastYear and table_a.date.

    but since Domo doesn't support JOIN on a BETWEEN clause you can do a cross apply. add a column to the tables _join_col = 1. then JOIN table_a to table_b on _join_col. then filter table_b.date between table table_a ...

    Now you can calculate COUNT DISTINCT for table_b.customer_id.

    You can now take this and join it back to your original activity table.


    Jae Wilson
    Check out my Domo Training YouTube Channel

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"