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

    Average line + Date filtering

    Antoine Chirac

      Hi everyone !

       

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

       

      Capture.PNG

       

      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
          Zhouyi Zhang

          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:

           

          [date field] >DATEADD('week',-2,DATETRUNC('week', TODAY()))

          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
            Antoine Chirac

            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
              Zhouyi Zhang

              Hi, Antoine

               

              Glad it works for you.

               

              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.

               

              [date field] >=DATEADD('week',-2,DATETRUNC('week', TODAY()))

              AND

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

               

              Please let me know if it is still a problem.

               

              ZZ