2 Replies Latest reply on Apr 24, 2018 6:48 AM by Michael Niven

    Calculated Fields with trending data

    Michael Niven

      So let's say I have a dataset with the following columns:

       

      COLUMN A               COLUMN B             COLUMN C   

      Description                   Fiscal Period             Total Sales

      Apple                            201801                      1

      Bird                               201802                      3

      Orange                          201803                      19

      Orange                          201804                      5

      Bird                               201804                      6

      Turtle                             201805                      2

      Apple                             201805                      2

       

       

      I am trying to create 3 calculated fields that sum the above as follows:

       

      • Latest Period - Basically, the most recent period (so it should be the greatest integer if Fiscal Period was int value).
      • Last 4 Periods - Most recent 4 periods (So, 05, 04, 03, 02)
      • Year-to-Date - All periods that fall under the current year (so anything with, "2018").

       

      This is my desired output (although I wish to have % change columns as well if there was a 4th column with Total Sales LY).

       

      Description
      Latest PeriodLast 4 PeriodsYear-to-Date
      Apple225
      Orange52424
      Bird066
      Turtle222

       

       

       

      How would i go about writing the proper calculated field to do this?