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.
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!
Sample Superstore.twbx 1.9 MB
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.
Sample Superstore Ty.twbx 2.0 MB
This is EXACTLY what I needed!
THANK YOU!! THANK YOU!!!