# I want to understand average sales within 1 year of client sign up

Member

I have a dataset where each row is an individual sale that contains the sale date, client start date, client ID, and sale total. I want to be able to chart the amount an average client spends within X time of their start date where X is a variable that I can alter from the card.

Ideally, I'd be able to split the ouput by year so I can compare how much an average client spent during their first 6 months in 2021 vs. 2022 vs 2023.

I've made a very simplified version of the dataset so you can get an idea of what I mean. any help is greatly appreciated

• Contributor

• 1. Define a variable for the number of days after client start date that you want to include. This variable can be added in the dashboard as a control and can be manipulated. I will refer to this variable as `Number of Days To Include`
• 2. Create a beastmode to calculate total client spend within the specified days:
• `sum(case when `date` >= `Client Start Date` and `date` ⇐ date_add(`Client Start Date`, interval `Number of Days to Include` day) then `Total` end)`

3. If you want to find the average client spend, divide this value by the distinct number of clients in the time interval.

`(sum(case when `date` >= `Client Start Date` and `date` ⇐ date_add(`Client Start Date`, interval `Number of Days to Include` day) then `Total` end)) `

`/ `

`(count(distinct case when `date` >= `Client Start Date` and `date` ⇐ date_add(`Client Start Date`, interval `Number of Days to Include` day) then `ClientID` end))`

` `

4. To split the output by year, make sure that the "graph by" setting on your visual is by year.

• Contributor

• 1. Define a variable for the number of days after client start date that you want to include. This variable can be added in the dashboard as a control and can be manipulated. I will refer to this variable as `Number of Days To Include`
• 2. Create a beastmode to calculate total client spend within the specified days:
• `sum(case when `date` >= `Client Start Date` and `date` ⇐ date_add(`Client Start Date`, interval `Number of Days to Include` day) then `Total` end)`

3. If you want to find the average client spend, divide this value by the distinct number of clients in the time interval.

`(sum(case when `date` >= `Client Start Date` and `date` ⇐ date_add(`Client Start Date`, interval `Number of Days to Include` day) then `Total` end)) `

`/ `

`(count(distinct case when `date` >= `Client Start Date` and `date` ⇐ date_add(`Client Start Date`, interval `Number of Days to Include` day) then `ClientID` end))`

` `

4. To split the output by year, make sure that the "graph by" setting on your visual is by year.

• Member

Wow, @nmizzell, that was everything I was looking for and more. Incredible stuff and I truly appreciate the walkthough you provided. 🙌. Many, many thanks!

• Contributor

Happy to help!