4 Replies Latest reply on Nov 8, 2018 2:55 AM by Mathis Eckhardt

    line chart with moving sum and two date filters

    Mathis Eckhardt

      Hello,

      I've been working with Tableau for some weeks now (so I'm new) and I'm encountering a problem I hope somebody can help me with.

      I've created and attached a simplified sample with the Superstore data (10.5.0)

       

      I visualized a time line (showing 12 month) of the moving sum over the previous 12 month (dimension "order date") of the measure "number of records". I did that with the help of the date lookup filter.

      Now the problem: I want to filter out the values whose ship date is also within the same time frame of each monthly moving sum.

      In other words, I want to visualize the number of records for each month whose order date AND ship date is within the previous 12 month time frame.

       

      I can't just put ship date in the filter box, because then the date values of the time frame are fixed and only one value in the timeline is correct. I thought about a calculated field, but I didn't find any approach.

      Can anybody help?

       

      EDIT: Sheet2 is only for checking the values from sheet1. It simply counts the number of records filtered by ship date and order date which has to be adjusted manually to one time frame (for example Oct17 + prev. 11month).

       

        • 1. Re: line chart with moving sum and two date filters
          Akram Ebrahim

          Hi Mathis,

           

          Take a copy of  order date from columns self  and place it in the filters. Right click and select Add to context

          Now the order date will turn into gray color on the filter

           

          On  dashboard right click ship date and select all values in context

           

          On next filters ship date 2 and order date 2 select only relevant values

          1 of 1 people found this helpful
          • 2. Re: line chart with moving sum and two date filters
            Mathis Eckhardt

            Hi Akram,

            thanks for the quick response and your solution!

            Unfortunately it does not yet deliever the correct results. The marked value for Oct17 in sheet1 should be the same as in sheet2.

            What I forgotten to say, is that sheet2 is only for checking the values from sheet1. It simply counts the number of records filtered by ship date and order date which has to be adjusted manually to one time frame (for example Oct17 + prev. 11month).

             

            • 3. Re: line chart with moving sum and two date filters
              Akram Ebrahim

              Hi Mathis,

               

              As already one ship date is there then why you have added ship date 2?.

              The order date might differ from ship date because shipping will be done after ordering item.

              As of now i have fixed the max values of sheet 1 and sheet 2 are same. Let me know is this solve your issue

              • 4. Re: line chart with moving sum and two date filters
                Mathis Eckhardt

                I'm sorry, the example I created obviously doesn't really help and I think I could not yet explain well enough what my goal is. I would like to try it one more time.

                 

                Maybe this would be a better headline: Moving sum with two different date dimensions?

                 

                My goal is to visualize the trend of the field failures (of products) in monthly values (and later the field failure rate). Each row of the data is one field failure. Each monthly value shall be the sum of the rows of the previous 30 month (why I'm using moving sum). Now the tricky part: I have to consider 2 different dates, the "failure date" and the "sale date" (in the example it would be ship date and order date). So only the field failures which have failure date AND sale date in this 30 month time slots shall be considered.

                Is there a possibility in Tableau to do that?

                 

                I would know how to build the moving sum with one date dimension, but not with two. I would know how to get the value for one single month (simply two date filters), but not for the Trendline.