2 Replies Latest reply on Apr 4, 2012 7:37 AM by branden.collingsworth

    calculated field for <value> from last december?


      I need to add a reference line to sheet1 of the attached dashboard that indicates the value for profit from december of the prior year.


      For my purposes, dates must be displayed as pages as they are in the attached dashboard.


      Currently, there is a reference line indicating profit from one year ago, created as a calculated filed using the table calculation formula WINDOW_SUM(sum([Profit]),-1,-1) computed using Year of Order Data.


      I'm thinking the solution to my problem will use a variation of this table calculation formula.  I'm hung up on the fact that the month - December- cannot depend on the structure of the table.



      Any help would be appreciated.

        • 1. Re: calculated field for <value> from last december?
          Tracy Rodgers

          Hi Branden,


          Would you be able to re-post the workbook as a twbx file? Thanks!



          • 2. Re: calculated field for <value> from last december?

            Sorry about that Tracy.


            I think I found a solution to the problem.  I calculated a new field, called December, that is zero for every month except December, where it is set to equal profit. I then created a table calculation using that new field, called Last December, that looks back 12 months across the date field  -- WINDOW_SUM(SUM([December]), -12, -1).


            I still don't fully understand how the table calculation works in this context.  Specifically, these relative values in the WINDOW_SUM function are looking at date fields in the pages shelf, which only works here because the months and the year are part of the same field but I found that they must be individually listed on the pages shelf.


            If anyone could explain what's going on here, or point me to the appropriate documentation, I'd be grateful.  I'm going to be using these types of calculation in bullet graphs to compare values (say, profit levels) to certain important values from the past (past profit, last years total profits, etc.).