3 Replies Latest reply on Jul 13, 2017 4:57 PM by Zhouyi Zhang

Average line + Date filtering

Hi everyone !

I'm working on a worksheet that deals with data from a ticket system called OTRS. Here it is :

What it does is displaying the tickets created for each day during previous weeks, each color representing one queue.

I have 2 problems :

1. I want to display the bars only from the 2 previous weeks. With a regular filter, you can either do "Previous Week", or what is done here is "Last 3 weeks", because if you choose "Last 2 weeks" it displays data from the previous week and the current week. So right now it displays data from the 2 previous weeks AND the current week. Except I don't want to display the current week. Is there a way to tell it to ignore data from the current week ?

2. I want to display an average line that does the average of tickets created on a day, the average being calculated with the values from all on the queues on a same day. For example for the first day here it would calculate the average between 8,4,3 and 28 and get a point. I have tried using a reference line but I could only manage to do a global average, calculated from every value in this chart, which is not what I want. Same for a quick calculation, unless I'm doing it wrong.

Thank you very much in advance !

Antoine

• 1. Re: Average line + Date filtering

Hi, Antoine

If you could provide a sample workbook would be more helpful.

I can make up some calculation for your reference if you can't provide.

first, you need create a filter to filter the date and only include last two weeks. Create something similar as below:

AND

[date field] < DATETRUNC('week', TODAY())

and then drag to filter and select true.

To your second question. Generally, you could create a calculation similar as below shown

AVG({ FIXED DATE([date field]),[queue]:SUM([tickets])})

But this may be various depending on your real case.

Hope this could help

ZZ

• 2. Re: Average line + Date filtering

Hi Zhouyi !

Both solutions work. Thank you very much ! There's just a little weird thing, with my 2 previous weeks filter, it doesn't take into account the very first day of these 2 weeks (June 26 in my case) and I don't really know why...

Antoine

• 3. Re: Average line + Date filtering

Hi, Antoine

To your question, it was my bad, I forget to add the "=" in the beginning of the week. please find below update with highlight in bold.