3 Replies Latest reply on Nov 8, 2018 9:59 PM by Bob Ngo

# Help with LOD - Fixed to a specific value of the fixed field

Hi all, - I'm using Tableau version 2018.3

Basically, I'm wanting to track differences in goal vs actual, with one column being the difference between a sum of a year total to the goal for that year, and then another column that shows the difference between the sum of the total for the year and a long term goal.

Basically this:

with the + 740 being the difference between the actual for 2018 and the final goal which happens in 2022 (4322).  I can get the yearly target difference easily enough,(sum(Actual) - sum(Goal)), but I'm having trouble getting the final target difference.  I feel that LOD is the way to get my subtrahend to be 4322.  So I went with this.

{FIXED datepart('year',[Year]) = 2022:sum([Goal])}

Which is getting me this:

I swear I've gotten this to work in different settings, but for some reason my brain is frozen and I can't see what I'm doing wrong.  Any help would be appreciated.  If you could explain to me why I am getting 19,446 in the other years but not 2022 that would be awesome too.

• ###### 1. Re: Help with LOD - Fixed to a specific value of the fixed field

Not exactly sure, but hope this helps.

You need to create nested LOD.

Thanks,

Shin

• ###### 2. Re: Help with LOD - Fixed to a specific value of the fixed field

Hi Bob,

You can use a simple table calculation for this.

Lookup(sum([Goal]), last())

Make sure it's set to calculate using Table Down and it'll get you the last goal number.

Put sum([Actual]) -   in front of it to subtract the last goal value from each rows Actual.

Best,

Paul

1 of 1 people found this helpful
• ###### 3. Re: Help with LOD - Fixed to a specific value of the fixed field

I tried both methods and Paul's worked when I applied filters while Shinichiro's did not.  I guess LOD was not the way to go after all.  Thanks to both for taking the time to help me out.