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

      AND

      [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"

      END

       

      Thank you