3 Replies Latest reply on Sep 14, 2018 7:46 AM by Jim Dehner

    Extending Date Calculated Field

    Graeme Elliott

      Hi all,

       

      In Sheet 3 of the attached workbook I have set up a table where I am looking at the difference between two months to get the value and percentage difference.

       

      My start month calculated field is:

       

      IF DATEDIFF('month', DATETRUNC('month', [Date]),

      {MAX(DATETRUNC('month', [Date]))})=12

      THEN [Data]

      END

       

      My end month calculated field is:

       

      IF DATEDIFF('month', DATETRUNC('month', [Date]),

      {MAX(DATETRUNC('month', [Date]))})=1

      THEN [Data]

      END

       

      The date fields pull the sum of data for the 12 months prior to my start date (in my sheet it is from May 2017) an sum of data for the current month in my sheet (April 2018). The two difference calculations are now showing the difference between the data of these two dates. What I would like to do is extend this so that I can reach the data for the start date being in April 2017 so I can compare April 2017 to April 2018.

       

      Is there a way I can amend my start month calculation so that it reaches to 13 months? 12 is the max it will go to, after that the value drops off (still trying to research why that is and understand it). Or is there even a better way of doing this? My thought process for the table was to get the total for two data periods, then find the difference, and then get the percentage.

        • 1. Re: Extending Date Calculated Field
          Jim Dehner

          Good morning

          YOur data "drops off" after 12 months because you have a filter on date set to 12 months - you have filtered out any older data

          so what to do?

          Remove the filter

          Add a Parameter where the user can input a  date OR you can use the Max(date) in the data set a date

          Then base your 12 month and 24 month calculated fields on the max date or parameter

           

          I'm not a real fan of datediff as a way to go (it will work) I like you use datetrunc combined with dateadd - but then again I have an engineering degree so I look at things a little differently

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Extending Date Calculated Field
            Graeme Elliott

            Oh my god! It really has been one of those weeks for me! I totally did not think about what was going on. I tried so many different variations with changing the formula and date range but somehow this one passed by me. Thanks very much (although I am super embarrassed!)

            • 3. Re: Extending Date Calculated Field
              Jim Dehner

              Dont be embarrassed - we've all been there -

              It is probably in the top 5 questions we see on the forum

               

              Glad to help out

              Jim