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?

    Pat McHale

      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))-

      (SUM(IF MONTH([Application Date])=MONTH(DATEADD('month',-1,TODAY())) AND YEAR([Application Date])=YEAR(DATEADD('month',-1,TODAY()))

      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?
          Joe Oppelt

          { 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?
            Joe Oppelt

            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?
              Pat McHale

              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?
                Joe Oppelt

                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?
                  Pat McHale

                  Hi Joe. Thanks again for your reply.

                   

                  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?
                    Joe Oppelt

                    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?
                      Pat McHale

                      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.