3 Replies Latest reply on Apr 4, 2016 2:48 PM by Joe Oppelt

    How do I calculate a value as of each date?

    Derek Buchholz

      I am somewhat new to Tableau and apologize in advance if this is a simple answer, though I have scoured many online helps and tried many different things and cannot come up with the solution.

       

      How do I get Tableau to show me this calculation as of each date?

       

      I have a calculation to find dollars per points.  I would like to see the value as of each date this year, starting at January 1 and going to the current date (or March 29 which is the latest date in my data set).  The calculation is this:

       

      ZN(

      (SUM([Invoice Details].[Line Item Total])

      + SUM([Dollars]))

      / SUM([Points])

         )

       

      When I simply display the calculation without any time dimension, I get 1.11, as expected. 

       

      However, when I add a time dimension, the value on the last date is 88 when I expected it to be 1.11.

       

      Additionally, I have tried all the Quick Table Calcs and none of them gives me what I expected.

       

      My data is structured such that, even though everything is coming from the same Excel file, I had to bring in the info as different data sources and blend because the joins were not making my calculations behave correctly.

       

      My workbook is attached.

        • 1. Re: How do I calculate a value as of each date?
          Joe Oppelt

          your results are a function of how you are blending stuff.

           

          See attached.  I put values on the marks so you can see what's happening in there.

           

          The sum from the secondary source is not breaking apart by date because you have no date field as part of the join.  So you're getting big sums for every day of a given month (I'm guessing it changes from month to month because TeamID activity changes from month to month.

           

          You need a date field to join on for all the data sources.  Then it will break the marks apart by date when you get the SUM from the secondary source.

          • 2. Re: How do I calculate a value as of each date?
            Derek Buchholz

            Thanks for that info.  So how do I correctly join these sources and/or re-structure my data?

             

            I just tried joining on date, and it takes out the line item total in my calculation (see attached).

            • 3. Re: How do I calculate a value as of each date?
              Joe Oppelt

              I don't know your data enough to understand what's there.

               

              I would expect some sort of transaction date.  Maybe [To Date] is that.

               

              To be blunt, I'll bet you don't even need to do multiple data sources, though I'm just guessing at that.  You assert that you needed to do that, but most times it's avoidable.


              To understand what's going on in there, I would need to immerse myself in your stuff, and I don't have bandwidth for that.  I'm swamped with my own work here.