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

vikrao · 2022-04-14T21:41:24+00:00

There was an error rendering this rich post.

vikrao

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

Find more posts tagged with

Visualization

Beast Mode

Comments

vikrao

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

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.

vikrao

Thanks Mark. This was very helpful.

jaeW_at_Onyx

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