3 Replies Latest reply on Dec 16, 2016 1:56 PM by Michael Summons

    Convert Date to Integer and use for calculations

    Michael Summons

      I've been searching the knowledge base and community discussions, but have not been able to find anything quite like what I'm looking for. So in trying to find ways to increase the performance of my dashboard, I attempted to revise some of my calculated fields that were using string-based parameters or date functions in the calculations. The parameters were easy to change from a string to an integer, but am a little confused when attempting to convert dates into integers.

       

      Below is the original script in the calculated field:

       

      //Calculates FY % Elapsed

      DATEDIFF('month',[Fiscal Start],(ATTR([Include Month])+17))/DATEDIFF('month',[Fiscal Start],[Fiscal End])

       

      This produces a percentage of the year elapsed based on the equivalent of (fiscal year start - current month) / (fiscal year start - fiscal year end)

       

      In trying to change the date fields to integers, I implemented the following script as a replacement:

       

      ABS(INT([Fiscal Start])-(INT([Include Month])+17))/ABS(INT([Fiscal Start])-INT([Fiscal End]))

       

      I originally tested each segment to see if this was even feasible and Tableau returned integers for both parts (23,932 & 142,504), which (in theory) should've calculated to 17%. Instead I'm getting returns like 65.747 or 6575%. So my question is, am what I am trying to do even an option? Maybe I'm missing a step? Why is Tableau returning an unexpected number?

       

       

       

        • 1. Re: Convert Date to Integer and use for calculations
          Walt Reed

          Hey Michael,

          Is it possible for you to attach a sample packaged workbook for us to look at?

           

          Walt

          • 2. Re: Convert Date to Integer and use for calculations
            Andrew Watson

            Are you summing the calculation? If so it could be a level of aggregation issue that could be solved by altering where the sum happens.

             

            If it is summed in your view this is what is happening: SUM(ABS(INT([Fiscal Start])-(INT([Include Month])+17))/ABS(INT([Fiscal Start])-INT([Fiscal End])))

             

            Perhaps you actually need it to do this:

             

            SUM(ABS(INT([Fiscal Start])-(INT([Include Month])+17)))/SUM(ABS(INT([Fiscal Start])-INT([Fiscal End])))

             

            You may even need the sum to happen deeper in the calculation.

             

            Out of interest is 17% the expected result? Is wrapping the date in INT giving you what you expect?

            • 3. Re: Convert Date to Integer and use for calculations
              Michael Summons

              So, I'm not sure what was going on with the dashboard itself, but was in the process of generating a dummy .twbx file to attach to this thread when I noticed that the script I posted above actually was working as intended. And yes, 17% was the desired result. There was something in the dashboard that I was working with that was preventing a correct % and still don't know what the issue is.

               

              This particular dashboard is blending 7 or 8 different data sources, so as a workaround I added the script as a calculated field in a different data source and brought it in to the dashboard by refreshing the data extract within the file. That seems to work. To be honest, I really don't see a difference in processing as the query times have not changed at all .