5 Replies Latest reply: Nov 27, 2015 8:51 AM by Stefano Caminiti RSS

    Display Previous Month Values in Pivot Table

      Hi,

      I've searched the forums extensively for this topic which has been discussed at depth, and tried implementing the suggested formulas with little success.

      Please see my pivot table below:

      error loading image

      The user is required to select a Year and Month in list boxes.

       

      The formula for This Month is:

      Sum(GLTranAmount)

       

      The formula for YTD (rolling financial year) is:

      Sum({$<Month =>} If(InYearToDate(GLTranDate, vMaxDate, 0, 7), GLTranAmount))

       

      The formula for Last Year Total (static) is:

      Sum({$<[FinYear]={$(=max([FinYear])-1)},Month=>} GLTranAmount)

       

      How can I best write the formula for Previous Month?

       

       

        • Display Previous Month Values in Pivot Table
          Johannes Sunden

          Hi Matt,

          I assume you have some kind of Date/Timestamp field from which you've extracted the Years and Months into their own fields.

          If you want to look at the previous month the easiest approach is to have a full date or something like YYYYMM and then use the function AddMonths.

          AddMonths(Timestamp,-1)

          This subtracts one month and you don't run into problems when going from Jan to Dec for example.

          //Jsn

            • Display Previous Month Values in Pivot Table

              jsn:

              Sum({$<Month={$(=(AddMonths(max(GLTranDate,0),-1)))}>} GLTranAmount)

              Along these lines? (not working)

                • Display Previous Month Values in Pivot Table
                  Johannes Sunden

                  Hi Matt,

                  First of all you can't just set the Month in the set analysis since if today is Jan and you want to look at the previous month you're also wrapping over to last year.

                  The quickest approach is likely to create a YearMonth field in the script. 200905, 200906 etc. and make sure it's formatted as a proper date.

                  Then you could apply:

                  Sum({$<YearMonth={$(=(AddMonths(YearMonth,-1)))}>} GLTranAmount)

                  This can of course be done on the fly in the expression and then applied on both Year and Month.. (beware of missing parentheses):

                  Sum({$<Month={$(=Month(AddMonths(date#(Year & Month,'YYYYMM'),-1)))}, Year={$(=Year(AddMonths(date#(Year & Month,'YYYYMM'),-1)))}>} GLTranAmount)

                  You might need a Num() around the month function too depending on if you use numeric or text format for display of your months.

                  I recommend going the script route as that makes it a whole let easier to maintain.