13 Replies Latest reply on Jan 4, 2019 1:43 PM by ChicagoTableauUser

# Formula help

I have a formula that gives me the latest status value from the audit trail (see below)

I now need to create another similar formula that gives the latest status value prior to a particular date so the max date prior to a particular date.

Is that possible?

Thanks for any help!

{ FIXED [Opportunity]:

max(

IF

//max date

({ FIXED [Opportunity],[Property]:

max(

DATETIME(

([Date (Opportunity AuditTrail)])

)

)

})

=[Date (Opportunity AuditTrail)]

then

if [Property]="Status" then

[New Value] end end )}

• ###### 1. Re: Formula help

I don't have a dataset that mimics what you are trying to do but I can step you through a concept that would provide direction

I noted you have a embedded an lod that returns a max data by opportunity and property - (BTW embedding the formula will make your sheet run slower - each record is checked individually and yo are recalculating the embedded formula)

so here's a thought

make an LOD calculation that returns the date by opportunity and property (all of them not just the max)

then create a second calculation to get the Max date based on the LOD

then you can use that max calc against opportunity audit trail date as you did above and use lookup(   ,-1) to get the previous date

Just a thought

Jim

1 of 1 people found this helpful
• ###### 2. Re: Formula help

Thanks Jim, can you help with the formula to pull all dates not just the max? I have the below but Im not able to just remove the "Max"

({ FIXED [Opportunity],[Property]:

max(if [Property]="Status" then

(

DATETIME(

([Date (Opportunity AuditTrail)])

)

)

end)})

• ###### 3. Re: Formula help

here is what you are trying to do -

you want to create a virtual layer in your data that is at a level above the base data - (thats what fixed lod's do)

but you want to create it in a way that the "MAX" record will return the value you want AND the record (with the same combination of dimensions) that precedes it will return the next to last value

Fixed will create combinations of the dimensions that precede the colon - in your case property and opportunity (not clear where "Status" comes in - are the multiple Opportunity Dates that are related to Property = status and you want the last and next to last OR is there only one Record with property = status and you want the record before that regardless of what property equals)

not easy to do this without seeing the data - even dummy data would help

in any event when creating the LOD tableau will look at each combination of property and opportunity (and maybe something else like date) and then aggregate what follows the colon by the aggregating function - if there are multiple records with the same combination of the dimensions that preceded the colon and you aggregate by max then you get the one record with the max value - if there is only a single record you get that single record value if you use max or min makes no difference

so what - if there are multiple records with property =status but they have different dates then include the data as a dimension that precedes the colon and use max

if there is only one record that for each "property whatever' and you want to get the record before status regardless of that value then you would want the max date withproperty = status and the max date that preceded it regardless -

Jim

• ###### 4. Re: Formula help

BTW if you send me the book with some dummy data it may be much easier based on the data

I am trying to avoid table calculations so you can use the value on different sheets without rebuilding a table

Jim

• ###### 5. Re: Formula help

Hi Jim,

I have attached a test workbook that hopefully demonstrates what im trying to do. My real data source doesn't include the MAX Status or the Old Value columns and thats what im trying to achieve with a calculation.

Thanks

• ###### 6. Re: Formula help

see the attached

there are 2 tabs the first uses lookup - a table calc

each uses this to find the max date for status

the lookup version then uses this

and returns this

it means you need a table - the second uses LODs

after finding the max determine the date difference between the max and each record

then the min > 0 (the next to last)

then the date

not table calcs but in table form it looks like this

if you just want the value it looks like this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 7. Re: Formula help

Thanks Jim, really appreciate the help.

For the next to last date I need it to be relative to the "date" I have outlined below in the table.

Thanks

• ###### 8. Re: Formula help

please decode the accounting speak into something that is directly related to the data set

thanks

Jim

1 of 1 people found this helpful

• ###### 10. Re: Formula help

doesn't help why 9/4 you wanted the next to last date?  what is nte data triggers 9/4 v 12/31 and you only wanted project = 'Status"?

1 of 1 people found this helpful
• ###### 11. Re: Formula help

Hi Jim, apologies for the confusion. Ultimately my goal is to add the "Old Value" column per below. My understanding was that I would need to calculate the date piece first but maybe not.

• ###### 12. Re: Formula help

see the attached

I can't do it without a lookup function

I have 2 final calculations to show you how do get the final table you want

the first includes some nulls - the second removes the nulls

first the max value in your table by opportunity

this will return the old value

set like this

it returns nulls for the first record in each opportunity

to get rid of the nulls I added this calculation

set like this

you can hide any column in the table  but you get this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 13. Re: Formula help

Thank you Jim!