1 of 1 people found this helpful
...so that was painful, going back to T8!! (...amazing how much Tableau has moved on, and how quickly you get used to the new calculation editor!)
Anyway...so pre-Tableau 9.0 (LoDs), the way to do an aggregation at a specified (non-VizLoD dependent) level was to use sets. Sets are assigned against a dimension (this would be the LoD part of a FIXED LoD), and the condition for entry to the set needs to be an Aggregate boolean test.
So we first right click on Payers, and select create set, and goto the condition tab
where I enter the following formula
SUM(IIF([Arrears]>=[Days Past Due - Greater than],[Past Due],0))>=[Delinquent Amount- Greater than]
So it SUMs (only where the [Arrarys, at Row Level, is >= to the parameter for this) and checks if this is greater than the other parameter. If it is the Payer (ID) goes into the set....and we can add to the filter shelf
We then need to only include these invoices...which is much easier as this can be done at row level. So I create a filter, which I set to true...
[Arrears > Threshold]
[Arrears]>[Days Past Due - Greater than]
and Voila the set returns any people who hit the amount owed threshold (only for invoices where the due date is > parameter) and the row level filter only returns those invoices.
Hope this does what you need, and makes sense...if not let me know.
Thank you so much for the quick response, Simon.
I added another row of test data to my source data and the Tableau workbook is not filtering as I expected. The row contains a Payer (4000862) who only has a past due amount of 57.67 but still shows up on the viz. Could you take one more look? Did I miss a step?
So you hadn't created the set, or put it on the filter shelf...and the row-level filter was set to use all (this needs to be set to true). So basically there were no filters!
Sorry about that Simon....I attached the wrong file.
Everything looks good, except in my actual data set (sorry cannot post due to confidentiality reason) I have multiple sales offices. In the screenshot below, you can see that Payer 4136783 has a past due balance in both "PL" and "STS" sales offices. How would I adjust my workbook so that the 1,710.66 for 4136783 doesn't show up in my viz since it does not meet the >25K >30 day threshold, but the 97,647.96 is displayed for STS?
Basically I need to add Sales Office into my calculation some how. Sorry if this is a juvenile question- I'm a very notice user.
...So if we want to also add Sales Office to the evaluation, we can create a concatenated field of Sales Office and Payer, and then build the set off that.
I then build the set of this, new, dimension using exactly the same conditional set formula as before. As we now have a field for both, the set is assessed at this level.
Thank you so much for all your help! I feel like I'm learning so much.
I have one quick follow-up question........which I hope is okay even though you have already answered my initial question.
In my actual dataset, I have a couple months worth of data that I would like to quick filter on. When I implemented your solution, I still receive rows that do not meet the filter criteria I have specified. Please look at the attached as I have added new data to illustrate my point.
On the attached, I filtered on July 2016 and set my filters to >30 days and >25,000. I spent most of my Friday trying to figure this out, however, I do not understand why those Payers that do not meet my filter criteria are still being displayed.
Thanks again for your time and all your efforts!
So the final piece of the jigsaw...
Due to the order of filtering operations within Tableau, set's are generated before any 'regular' dimension filters are added...so in essence filtering (as you have on date) has no affect on 'who' is in the set, as it is calculated over the entire dataset. Below shows the whole chain.
So in order to 'bump' a dimension filter up the pipeline, we need to make it an 'in context' filter.
one you 'Add to context' on your date-filter...the set will now get created on the remaining data. btw when you update to 10 FIXED LoDs work the same way.
Simon, I cannot thank you enough!
Thank you so much for your time