3 Replies Latest reply: Jan 21, 2011 7:15 PM by Karl Pover RSS

    Trending on Fiscal year


      I have 3 Fiscal year month


      Fiscal Year Periods

      Oct FY11 Nov FY 11 Dec FY11 3 month Trend

      55 75 85 arrow up or arrow down?? (how to calculated this)


      Question 2:

      Last Fiscal Month Current Fiscal month

      76.7 (compare the current month value to last fiscal month) and to show graphically arrow up or down


      Thanks for your help


        • Trending on Fiscal year
          Karl Pover

          One way you can do this is with set analysis. First, calculate the current fiscal month:

          sum({$<[Fiscal Period]={'Dec FY11'} >} Amount)

          To do the 3 month trend I assume you ignore the second month and just evaluate if the 3rd month is greater than the 1st month. So,

          if(sum({$<[Fiscal Period]={'Dec FY11'} >} Amount)>sum({$<[Fiscal Period]={'Oct FY11'} >} Amount),
          'qmem://<bundled>/BuiltIn/arrow_n_g.png', 'qmem://<bundled>/BuiltIn/arrow_d_r.png')

          and to compare the past fiscal period it would be something similar.

          Of course it would be better to make the fiscal period dynamic in the expression, but I would need to know your date format for Fiscal Period, or is it a string?


            • Trending on Fiscal year

              We use 010/2010 , 011/2010 ,012/2010.

              We have another column where we have JUL FY10. I suppose we can use either one.


                • Trending on Fiscal year
                  Karl Pover

                  It would be easier if you have a some date type column to back up the string value. In that way it will be easier to calculate the previous or previous two periods.

                  In the script, do something like the following:

                  Load ....
                  date(date#(right([Fiscal Period],7),'MM/YYYY')) as [Fiscal Date]

                  Then use Fiscal Date in the following way imagining that the user is going to select a [Fiscal Period]:

                  {$<[Fiscal Period]=, [Fiscal Date]={'$(=date(max([Fiscal Date])))'}>}

                  for the current month

                  {$<[Fiscal Period]=, [Fiscal Date]={'$(=date(addmonths(max([Fiscal Date]),-1)))'}>}

                  for the previous month

                  {$<[Fiscal Period]=, [Fiscal Date]={'$(=date(addmonths(max([Fiscal Date]),-2)))'}>}

                  for 2 months ago