9 Replies Latest reply on Aug 16, 2018 2:26 PM by swaroop.gantela

    Fixing to the previous fiscal year

    Kevin Pratt

      Hi all,


      First, thank you all for engaging, I am on these threads daily.  I'm using 2018.1 and a sample workbook is attached.


      As for my question, I am trying to label my regions based on a SUM of the previous fiscal year's sales (size A, B, C or D).


      Ideally this is dynamic so I don't have to update it after we change fiscal years.  FY is August through July.


      My thought is


      1- Create a field [Previous FY Sales] that isolates the previous fiscal year's sales, using something like this below, but dynamic.  THIS IS THE PART I NEED HELP WITH.


      SUM({ FIXED [Region] :

      SUM( {FIXED [Region], [Order Date] >= DATE('8/1/2016')


      [Order Date] <= DATE('7/31/2017'): SUM([Sales])}) })


      2- Use the newly created [Previous FY Sales] create a new label field [Current Region Size], like this


      IF [Previous FY Sales] <=400000 then "A"

      ELSEIF [Previous FY Sales] <=600000 then "B"

      ELSEIF [Previous FY Sales] <=700000 then "C"

      ELSE "D"



      Thank you