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).


      Latest PeriodLast 4 PeriodsYear-to-Date




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