4 Replies Latest reply on Aug 20, 2018 12:29 PM by Ken Flerlage

# Using the lookup to calculate the difference of cash flow amount between the selected date and the day before the selected date.

Hi, I'm using the lookup function to calculate the difference of cash flow amount between two dates, the last date and the day before the last date

ZN(SUM([Cash Flow Amt Cad])) - LOOKUP(ZN(SUM([Cash Flow Amt Cad])), 1).

The formula is working, however I should always select the two dates.

Is there any way to select only one date and the calculation is done between the selected date and the day before the selected date. • ###### 1. Re: Using the lookup to calculate the difference of cash flow amount between the selected date and the day before the selected date.

Your workbook doesn't include the primary data source, so it's prompting me to log into your Tableau Server. Can you reattach it with the data?

Without the data, I can't give a complete solution, but I can tell you that the problem is that you're using a filter to control this process. That is filtering the entire data set, thus the need to select both dates. I'd suggest that you change this to a parameter (let's call it "Last Date"). The user will select a single date from the parameter.

Then you'll need to use a couple of calculated fields:

Last Date Cash Flow

IF [Value Dt] = [Last Date] THEN

[Cash Flow Amt Cad]

END

Last Date-1 Cash Flow

IF [Value Dt] = DATEADD('day', -1, [Last Date]) THEN

[Cash Flow Amt Cad]

END

Difference

[Last Date Cash Flow] - [Last Date-1 Cash Flow]

Hope that helps!

• ###### 2. Re: Using the lookup to calculate the difference of cash flow amount between the selected date and the day before the selected date.

Thanks alots, it's working

• ###### 3. Re: Using the lookup to calculate the difference of cash flow amount between the selected date and the day before the selected date.

Great! Would you please go to the original post (not from your inbox) and mark my response as the "Correct Answer". This will allow us to close this thread as well as allow people to quickly find the answer in the future. Thanks!

• ###### 4. Re: Using the lookup to calculate the difference of cash flow amount between the selected date and the day before the selected date.

To be more clear, can you mark the actual correct answer (i.e. my answer which helped you out) as the correct answer, rather than the one you selected? Sorry to be a pain--it'll just make people's lives easier in the future when they search for similar problems.