3 Replies Latest reply on Nov 20, 2018 6:51 AM by Johannes Marthinussen

Ratio between two date periods

Hi all,

Sorry if this is very simple, but been struggling massively to sort it, so thanks for any advice in advance.

I want to create a ratio between the average distance ran in a 4 week period and 1 week period.

Tried using filter dates and custom values but didn't manage to make it for both. Tried making a calculation for it, but is didn't work.

Any suggestions on how to solve this issue?

Thanks

Johannes M

• 1. Re: Ratio between two date periods

Create a calc:

if [Date] >= (start date) and [Date] <= (end date) then [Total Distance] END

However you choose to get start- and end-date in there is up to you.

That calc will store the distance for all rows within the date.  All other rows will have null.

AVG([that calc]) will get you the average you need -- if you aren't filtering out rows (at least if you don't filter out rows that contain the date range.)

2 of 2 people found this helpful
• 2. Re: Ratio between two date periods

Also:

{ FIXED : AVG( if [Date] >= (start date) and [Date] <= (end date) then [Total Distance] END ) }

That will create a calc value that will be placed on every row.  Same value on all rows.  It will give you the same average result, even if you filter out the rows that comprise the date range.

2 of 2 people found this helpful
• 3. Re: Ratio between two date periods

Thanks a lot Joe, I finally managed to sort it using your first formula.

Appreciate it