8 Replies Latest reply on Aug 30, 2016 1:53 PM by Mikey Michaels

# Calculated fiel HELP

Hello All,

I hope this message finds you well.

I need some help adjusting my Calculated Field [Past Due - Significant Accounts] on the attached. The calculation is connected to my parameter (Delinquent Amount - Greater than).

My objective is to present all Payers where Arrears is >30 days AND the total past due is >25,000. Right now,  my Calculated Field [Past Due - Significant Accounts]  is looking at the invoice level to see if it is >25,000 thus omitting invoice 80769410 and 80761000 from the total. How can I adjust my TC to show a total of 55,481 instead of 41,781?

*Please note, I still working with 8.3 (ggrrrrr) so LOD calculation will not answer my question.

Cheers,

Mikey

• ###### 1. Re: Calculated fiel HELP

hi Mickey,

...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.

1 of 1 people found this helpful
• ###### 2. Re: Calculated fiel HELP

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?

Thanks!

• ###### 3. Re: Calculated fiel HELP

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!

• ###### 4. Re: Calculated fiel HELP

DOH!

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.

Thanks!

• ###### 5. Re: Calculated fiel HELP

...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.

[Sales Office_Payer]

[Sales Office]+'_'+[Payer]

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.

• ###### 6. Re: Calculated fiel HELP

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.

Cheers,

Mikey

• ###### 7. Re: Calculated fiel HELP

hi Mikey,

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.

• ###### 8. Re: Calculated fiel HELP

Simon, I cannot thank you enough!

Thank you so much for your time