1 2 Previous Next 18 Replies Latest reply on Jul 26, 2016 12:52 PM by P N

# Average across different time window?

Hi, I am trying to create a viz with multiple average lines, with each average corresponding to a different time window, e.g., average across 1/1-1/31, 2/1-5/19, 5/19-6/15, etc.

Any help is appreciated!

• ###### 1. Re: Average across different time window?

You can create a Parameter for Date range, which can be used to select data points for averaging using an LOD expression as a calculated field.

• ###### 2. Re: Average across different time window?

I'm attaching a file to illustrate my question above. Any examples would be great.

• ###### 3. Re: Average across different time window?

Do you want to show average value by date within a certain date range? Or do you want an overall average value within a date range?

• ###### 4. Re: Average across different time window?

I'd like to show overall average value within a date range. E.g., in the file attached above, average from 1/1-1/8 is 66; from 1/9 - 1/31 is 28, etc. Thx.

• ###### 5. Re: Average across different time window?

Ok, something like this?

Range 1 = date is between 1/31/2016 and 1/1/2016

Range 2 = date is between 2/1/2016 and 5/19/2016

Range 3 = date is greater than 5/19/2016

I created a new Dimension for Date Range

if [Date] <=date("1/31/2016") and [Date]>=date("1/1/2016") then 'Range 1'

elseif [Date] <=date("5/19/2016") and [Date]>=date("2/1/2016") then 'Range 2'

elseif [Date]>=date("5/20/2016") then 'Range 3'

END

• ###### 6. Re: Average across different time window?

That's the right idea but I'd like to display several average lines on the line chart (run graph) that I attached previously instead of bar charts.

• ###### 7. Re: Average across different time window?

That's a little confusing since you will have only one average value per date range; you cannot have a line with a single value.

• ###### 8. Re: Average across different time window?

I'm attaching a chart with what I have in mind that the final viz will look like. Hopefully it's more clear that way. Let me know your thoughts.

Thx.

• ###### 9. Re: Average across different time window?

Ok, so looks like you want to show the individual averages for each day then an overall average for the date range. Is that correct?

• ###### 10. Re: Average across different time window?

I guess we are interpreting it differently. I want to show an overall average for the date range, meaning, for an overall average of 1/1-1/3 for example, i want to show the average = (100+40+92+45+78+25)/6

• ###### 11. Re: Average across different time window?

Sorry, I am not able to get the exact view you need. I am able to calculate the average value over a date range, but not able to show on the line chart the way you are looking for.

Hopefully, someone else will have an answer.

Good luck.

• ###### 12. Re: Average across different time window?

Hi Tze/Naveen:

The best I could get was to separate the 3 ranges and create the average line for it. When you edit the x-axis select "Independent axis ranges for each row or column".

Though not a continuous graph you were looking for, but captures the requirement.

Thanks

• ###### 13. Re: Average across different time window?

This looks like a good trick!

I was thinking about calculating another field that could have the range average values, then adding it to the shelf so it could be shown on the same chart.

• ###### 14. Re: Average across different time window?

Hi PN,

That looks good. Can you share your workbook so I can see how you created the range?

Thx.

Regards,

Tze

1 2 Previous Next