2 Replies Latest reply on Feb 23, 2017 3:11 AM by Simon Runc

    Closing balance using LOD!?

    Sebastian Flucke

      Hi, everybody!

       

      What I need is a closing balance aggregation for a stock balance on higher levels of the time dimension like month, quarter etc.:

      • the total of a month should be the balance of the last day of month
      • regarding the sample below the December's total should be 53,0 - not 251

       

      I tried to follow the ideas of "closing balance with LOD" regarding sample #9 of this blog post https://www.tableau.com/about/blog/LOD-expressions:

      • MaxDate measure as "{INCLUDE : MAX([Date])}"
      • Closing Measure as "IF [MaxDate] = [Date] THEN [Measure 3] else 0 END"
      • having a look at the data all seems to be fine...
        Screenshot1.JPG
      • ...but unfortunately the aggregate of 0+0+0+0+0+53 always sticks as 251

       

      What I'm missing here - or do I need to use a complete different approach?

       

      BTW: I'm working with Tableau 10.0 Desktop, twbx is attached!

       

      Kind regards!

      Sebastian

        • 1. Re: Closing balance using LOD!?
          Bhujang Hundre

          Hi Sebastian,

           

          I don't think so it is possible to achieve it in single sheet, if you make multiple sheets and then combine in dashboard it should work.

           

          You are almost near to the solution, using MAX of Date.

           

          Regards

          Bhujang

          • 2. Re: Closing balance using LOD!?
            Simon Runc

            hi Sebastian,

             

            So not sure this is exactly what you want...but let me know.

             

            So the first thing was to create a dynamic max date which returns the last day of each month, for each ID. (as I'm using a fixed I changed your date filter to "in context" so it would affect the results)

            [Max Date Per Month/ID]

            {FIXED DATETRUNC('month',[Date]), [ID]: MAX([Date])}

             

            Once I have this I can pull out just the last day's measure 3...

            [Closing Measure 3 - SR]

            IF [Max Date Per Month/ID] = [Date] THEN [Measure 3] END

             

            so the final bit is the Grand Totals...this is taken from the following (fantastic!) blog Customizing Grand Totals – Part 2 | Drawing with Numbers (I'd also recommend part 1 and 3!)

             

            [Measure 3 with GT]

            IF MIN([Date]) = MAX([Date]) THEN SUM([Measure 3]) ELSE SUM([Closing Measure 3 - SR])END

             

            Tableau creates Totals (unlike Excel) not by adding the "cells", but by removing the Dimension (Date in your case) and re-calculating. So where MIN([Date]) = MAX([Date]) we have a regular row, where they don't we have a Grand Total. As such we can change what is returned when!

             

             

            hope this helps.