4 Replies Latest reply on Dec 29, 2016 12:13 PM by Ty Stackhouse

    Filtering or Sorting by Most Recent Completed Month

    Ty Stackhouse


      I have a problem that may be resolved by filtering or sorting and I cannot seem to find a calculation that works for this situation.  I have previously posted  sample packaged workbooks but I am not getting quite what I need.

      Below is a screen shot of the actual workbook with some sensitive information blocked out. What I am looking to do is either sort or filter by the "Exception" row in the most recent fully completed month(which is November, given that today is December 29th).

      Circled to the right are the numbers that I would like to either filter or sort by. What we are trying to pinpoint is any item whose quantity falls outside one standard deviation of the moving average. Any item that falls outside of this range is

      marked by a "1" in the Exception row, any item that is inside one standard deviation is marked with a "0" in the Exception row. I would like to either bring all of the items with a "1"  to the top by sorting, or filter out any items that have a "0" in the most recently completed month.


      Any Suggestions??



      Filter by Previous Month.PNG

        • 1. Re: Filtering or Sorting by Most Recent Completed Month
          Joe Oppelt

          First step is to identify your target month.  Do you already have a way to do that?


          If you do, then you can do a calc that collects the [exception] value only for the target month.


          You then sort (or filter) by that calc.


          So much of the specifics of this will depend on what you have on that sheet.  Are these calcs?  Table calcs?  LODs?  A workbook will let me help you with that.

          • 2. Re: Filtering or Sorting by Most Recent Completed Month
            Ty Stackhouse

            Attached is a sample workbook.  My target month will always be the most recent fully completed month, so on January 1st I would like something that will sort/filter on December.

            As of right now November is the last full month I have to work with. The reason I am doing this is because my cube is pulling live data.

            If I would compare a partial month to a full month it would look like my quantity filled is way down, when in reality I am comparing a 30 days worth of sales on 15 days etc..

            Thank you for the help!

            • 3. Re: Filtering or Sorting by Most Recent Completed Month
              Joe Oppelt

              In the attached I created Sheet2 (duplicate of Sheet 1.)


              I created [Max Date in Data Source].  A FIXED LOD will churn through your data source before the filters are applied.  So this grabs your biggest date in the data source.  I made an assumption that whatever month is your last date, that's NOT the last closed month.


              Next I made a calc to hack up the last day of the month prior to the last date in the data source.  See [Last closed month].  Datetrunc() truncates a date to the lowest value of the selected time period.  Since I selected "month", it truncates that date to the first of the month.  That is embedded in a DATEADD() function.  I am adding negative 1 to subtract a day.  I placed these two values in the title of the sheet so you can see what it computes to.  (You can actually combine all this into one calc in the end.  I just compartmentalized it so you can see the steps I took.)  And I wrapped that whole thing in the DATE() function because initially Tableay was making this a date-time output.


              I created a table calc called [Save target Exclusions].  Take a look at that.  It says, "When we're in the proper cell on the sheet, save the [Exclusion] value."  It does this for all months, but only the target month's value gets saves.  I wrapped all this in the ZN() function because I want to force zeros where no data exists (months where there is no [Quantity].)  Otherwise it was loading NULL, and for something I will do later on, I don't want NULLs.


              Now go to Sheet 3.  I created another calc just like the other SAVE calc, only I wrapped this in the WINDOW_MAX() function.  (This one is named "...exclusion" instead of "...exclusions" (plural),).  THIS is what you are really shooting for.  On Sheet 3 I put it on the ROWS shelf.  (When you duplicate this step, don't panic.  Initially when  you put it in the shelf it goes on as a "continuous" field and tries to put bars or lines on your sheet.  Right click on it, change it to DISCRETE, and it will change to an actual number value as you see on Sheet 3.)  Now go to sheet 4.


              On sheet 4 I moved [Save Target Exclusion] from the end of the ROWS pills to the front of the list.  Now it clusters all the zeros together, then all the 1s.  This is why I didn't want NULL in there.  It would put all the NULLS together, separate from the 0s.  Messy.  This trick of moving the pill to the front is how you get Tableau to sort on a table calc.  You can uncheck "Show header" for this pill and the whole column's display will hide.  (Users don't need to see this.)  Note that you can't direct "asc" or "desc" on this sorting.  If you want to sort this descending, change the calc to multiply the results by -1, and the values will essentially reverse themselves.


              On sheet 5 I moved that calc from ROWS to FILTER.  Now I filtered for value = 1.

              1 of 1 people found this helpful
              • 4. Re: Filtering or Sorting by Most Recent Completed Month
                Ty Stackhouse

                This is EXACTLY what I needed!

                THANK YOU!! THANK YOU!!!