7 Replies Latest reply on Feb 14, 2019 9:00 AM by Pat McHale

# How do I subtract the latest month's value from the previous month's value dynamically?

I am trying to find the difference between the number of Loan Applications between this month, last month, and 2 months ago. The issue is that the latest data is from March 2018 so using the Today() function doesn't work. I need to find a way to essentially replace Today() with the most recent day in the dataset and start the subtraction from there.

I have tried using Window_Max and Max(Date) etc. but then I get aggregation problems. I have also looked at anchoring dates and using LOD but have had no luck.

The formula I am using to calculate the difference between the latest months number of applications from the previous month is:

(SUM(IF MONTH([Application Date])=MONTH(TODAY()) AND YEAR([Application Date])=YEAR(TODAY()) AND [Loan Status]="Active" THEN 1 END)/

SUM(IF MONTH([Application Date])=MONTH(TODAY()) AND YEAR([Application Date])=YEAR(TODAY()) THEN 1 END))-

AND [Loan Status]="Active" THEN 1 END)/SUM(IF MONTH([Application Date])=MONTH(DATEADD('month',-1,TODAY()))

AND YEAR([Application Date])=YEAR(DATEADD('month',-1,TODAY())) THEN 1 END))

Does anybody know how I can fix this problem?

I have attached a sample of the dataset.

• ###### 1. Re: How do I subtract the latest month's value from the previous month's value dynamically?

{ FIXED : MAX([Application Date]) }

will tell you the last date in your data source.

• ###### 2. Re: How do I subtract the latest month's value from the previous month's value dynamically?

And it will give you that max date no matter what filtering you have on a sheet.  The FIXED LOD evaluates before any filters are applied in Tableau's order of operation.

• ###### 3. Re: How do I subtract the latest month's value from the previous month's value dynamically?

Hi Joe. Thanks for your response. I changed the formula (below) and now use { FIXED : MAX([Application Date]) } but this still does not display when I create a text sheet saying "The difference in live loans between this month and last is <LastMonthDiffCalculatedField>". Do you know if there is anything else wrong?

(SUM(IF MONTH([Application Date])=MONTH({ FIXED : MAX([Application Date]) }) AND YEAR([Application Date])=YEAR({ FIXED : MAX([Application Date]) }) AND [Loan Status]="Live" THEN 1 END)/

SUM(IF MONTH([Application Date])=MONTH({ FIXED : MAX([Application Date]) }) AND YEAR([Application Date])=YEAR({ FIXED : MAX([Application Date]) }) THEN 1 END))-(SUM(IF MONTH([Application Date])

=MONTH(DATEADD('month',-1,{ FIXED : MAX([Application Date]) })) AND YEAR([Application Date])=YEAR(DATEADD('month',-1,{ FIXED : MAX([Application Date]) })) AND [Loan Status]="Live" THEN 1 END)/

SUM(IF MONTH([Application Date])=MONTH(DATEADD('month',-1,{ FIXED : MAX([Application Date]) })) AND YEAR([Application Date])=YEAR(DATEADD('month',-1,{ FIXED : MAX([Application Date]) })) THEN 1 END))

Appreciate the help.

• ###### 4. Re: How do I subtract the latest month's value from the previous month's value dynamically?

I would create a separate calc that does the FIXED LOD.

And then, when you want to compare month/year of some date to month/year of another, it's easier to do this:

IF DATETRUNC('month', [Date]) = DATETRUNC('month', [Last data date calc]) THEN ...

The DATETRUNC function "truncates" the input date to the beginning of the requested period.  So for instance, if you were to do:

DATETRUNC('year', TODAY() ) (where today is 2/13/2019) the result is 1/1/2019.

DATETRUNC('week', TODAY() ) the result is 2/10/2019

DATETRUNC('quarter' TODAY() ) the result is 1/1/2019.

Etc.

It's far more direct and compact than individually comparing year and month.  Less chance for logic errors.

And you can use the DATETRUNC nested in a DATEADD.

DATEADD('month',-1, DATETRUNC('month', [Last data date calc]) )  // (If Last Date is March 10, 2019, the result of that would be Feb 1 2019.)

---

Something separate:  You are doing:  "The difference in live loans between this month and last is <LastMonthDiffCalculatedField>"

Are you doing that in a calc?  If so you actually want to do this:

"The difference in live loans between this month and last is " + STR([LastMonthDiffCalculatedField])

That will convert your non-string field to string and add it to the string of text you need.

(If you're doing that in the label edit box, then never mind...)

• ###### 5. Re: How do I subtract the latest month's value from the previous month's value dynamically?

That certainly helped but I am still finding an issue when trying to get a value for the Previous Month.

I think the line is causing the problem. The result returns blank even though the data itself says otherwise.

SUM(IF DATETRUNC('month',[Application Date])=MONTH(DATEADD('month',-1, DATETRUNC('month',[FixedLOD]))) AND [Loan Status]="Live" THEN 1 END

In relation to your second point, I'm doing this is the label edit box.

Thanks.

• ###### 6. Re: How do I subtract the latest month's value from the previous month's value dynamically?

You need to remove the MONTH() function:

SUM(IF DATETRUNC('month',[Application Date])=DATEADD('month',-1, DATETRUNC('month',[FixedLOD])) AND [Loan Status]="Live" THEN 1 END

The first part of that IF statement results in a date value.

The MONTH() function returns an integer value.  They'll never match that way.  Without the MONTH(), you'll be comparing dates to dates.

One last thing:  Is your [Application Date] field a DATE field?  Or a DATE-TIME field?  If DATE-TIME, Tableau will preserve the TIME portion of the values, and you rarely get matches on things like this.  If anything in all of this is a DATE-TIME field, wrap all these components in the DATE() function to make the values just DATE values:

SUM(IF DATE(DATETRUNC('month',[Application Date])) = DATE(DATEADD('month',-1, DATETRUNC('month',[FixedLOD])))

AND [Loan Status]="Live" THEN 1 END

• ###### 7. Re: How do I subtract the latest month's value from the previous month's value dynamically?

That worked. Thanks so much Joe.

I also found that using the DATEDIFF on the orignal { FIXED : MAX([Application Date]) } statement to get the previous month also works.

[Application Date] is a DATE field but that's good info to know.

Thanks again, really appreciate it.