8 Replies Latest reply on Oct 11, 2019 4:04 PM by Ali Rowshanaei

# Calculate daily average over specific date range

Hi,

I would like to create a calculation for average daily count of users over a specific date range regardless of the date range selected in the view. I want to use this for a reference line in a line chart.

The line charts shows daily active users for the past 7 days. I want to add a reference line which is the average daily users for the past 3 months.

I know I can create a calc like this which would give me the average daily users.

AVG({ INCLUDE [Event Date] : COUNTD([User Id])})

However, since my view has a the event date of past 7 days filtered, that won't work. Essentially I am wondering if I can create an LOD which would do this daily average for the past x number of Event Dates regardless of the view filter on Event Date.

Thanks

• ###### 1. Re: Calculate daily average over specific date range

Hi Ali,

You need to use Fixed Calculations

({Fixed  :SUM(IF DATEDIFF('month',[Order Date],{MAX([Order Date])})<=2

THEN [Sales] END)})/

(({Fixed :SUM(IF DATEDIFF('month',[Order Date],{MAX([Order Date])})<=2

THEN [Number of Records] END)}))

Find the attached workbook

Hope this helps

BR,

NB

• ###### 2. Re: Calculate daily average over specific date range

Hi Naveen,

Thanks for the quick response. I believe that helps but won't quite do what I am after here. The issue is that the formula you provided calculates the average sales per transaction essentially. What I need is slightly different. I need the average daily distinct users over the past 3 months. This means I first need to basically partition by event day, do a count distinct on user Id. This will get me count of users for each day. I will then need to average across the previous 3 months. Now again, the challenge is that the view is only showing the past 7 days.

Thanks

• ###### 3. Re: Calculate daily average over specific date range

Hi Ali,

Fixed add --> Datetrunc('day', datefield) and keep the calculation as it is numerator - Every Day count the distinct users and sum up for 3 months

denominator you can keep intact - number of days in 3 Months

Hope this helps

BR,

NB

• ###### 4. Re: Calculate daily average over specific date range

Hi Naveen,

Sorry I am not quite following what you are suggesting (probably my lack of Tableau skills). My date field is [Event Date] and my user Id field is [User Id]. My data set has multiple transactions per day per user Id and again, the complication is that [Event Date] is set to last 7 days in my view.

I came up with this formula (which kind of hardcodes the date range to the last 90 days):

SUM(

{Fixed DATETRUNC('day',[Event Date]) : COUNTD(

IF [Event Date] > TODAY() - 90 AND [Event Date] < TODAY()

THEN [User Id] END)

})

/ 90

However, the issue is that the formula is impacted by the [Event Date] filter in my view. I want the formula to return last 90 days average regardless of the date filter.

Thanks

• ###### 5. Re: Calculate daily average over specific date range

remove the event date from context so that it wont be affected on the calcualtion

BR,

NB

• ###### 6. Re: Calculate daily average over specific date range

Not sure how to do that. I know I can add a dimension as a context filter but how would I remove it from context?

• ###### 7. Re: Calculate daily average over specific date range

Hi Ali,

BR,

NB

1 of 1 people found this helpful
• ###### 8. Re: Calculate daily average over specific date range

I am not sure if I follow, I didn't have anything in the context in the first place so I won't have an option to remove it. I have attached a workbook to maybe help with this. Again, as you can see, the issue is that since we want to do a daily average before constraining it the previous 3 months, the even date dimension impacts the calculation and voids our 3 months.