5 Replies Latest reply on Oct 1, 2015 12:21 PM by Rody Zakovich

# LOD Challenge: Max of date less than date in current record

I feel like this should be solvable with LOD expressions, but can't figure it out. The first 2 blue columns are in the data source. The last column is my LOD calculation. There is no guarantee that the progression of dates will be predictable (i.e. qtrly, monthly, etc.) so it can't be done with "math."

I want to do something like:

{Fixed [Claim number] : max(if [Valued as of] <= THIS RECORD's [Valued as of] then [Valued as of] else null end) }

Seems like circular logic. Any ideas?

Claim numberValued as ofPrevious Valuation
A12/31/13null
A3/31/1412/31/13
A5/31/143/31/14
A6/30/145/31/14
A7/31/146/30/14
• ###### 1. Re: LOD Challenge: Max of date less than date in current record

Hello Ira,

What is the end goal? The chart you are showing can be accomplished using a LOOKUP function.

Is there any particular reason you are looking to use an LoD?

Regards,

Rody

• ###### 2. Re: LOD Challenge: Max of date less than date in current record

What I showed was a simplification to illustrate the issue. If I can get

the date sorted out with an LOD calc, it would enable a whole series of

secondary computations that I am sure would be easier and more elegant and

flexible with LOD calcs.

--

Ira Skop

www.shelterislandrisk.com

646-257-3807

On Thu, Oct 1, 2015 at 7:25 PM, Rody Zakovich <

• ###### 3. Re: LOD Challenge: Max of date less than date in current record

What you do here all depends on what the end goal is.

The only iterative calculation Tableau has is PREVIOUS_VALUE(), but we can use LOOKUP() as well in certain situations.

LoDs are simply a means of returning aggregated results to a row level based on the dimensions addressed in the calc (And the vizLoD for INCLUDE/EXLCUDE). So I'm not sure if an LoD would work for this particular situation.

Could you provide more detail on what you are trying to accomplish, as well as, an example workbook?

Getting the Most From the Tableau Forums

Regards,

Rody

• ###### 4. Re: LOD Challenge: Max of date less than date in current record

I know that what I do depends on the end goal, and I appreciate your desire to help solve my business problem. And I actually know how to solve it with table calcs.

I've been working a lot with LOD calcs lately, and I'm trying to try them out in different scenarios to see what they can do and how/if I can use them to address certain challenges. So the question I asked reflects the problem I want to solve - can a LOD calc be written to do what I want? That is my only end goal at this point. I know I can write one to give me the max value, and thus the one before the max, etc. I was looking at this as an extension of that but got stumped.

I could tear apart my data set to send an example, but I think the table I created is simple and shows everything necessary to illustrate the question.

Anyone else have an idea?

• ###### 5. Re: LOD Challenge: Max of date less than date in current record

Ira,

No, an LoD calc cannot do what you are trying to do here.

Rody