locked
Help with a Calculated Column RRS feed

  • Question

  • Hi guys, was hoping for a relatively quick answer to a Calculated column query I have using MOSS 2007.

    Tried a few different ways of presenting this data in the list but so far no luck and done some research and still cant see how to do this, so trying my luck here :)

    Essentially I have a few columns:

    • Choice column: Listing of Months (Jan to Dec)
    • Member column: Amount of members
    • Calculated column: Trying to increment the amount of members per month

    So as easy as I can try to explain this is; I am trying to add up the amount of members per month as a running total using the above column structure.

    Example:

    • July: 500, August: 300, September 200
    • So for July the entry would be 500 because it is the start of the Financial Year
    • August entry would be 800. July 500 + August 300
    • September entry would be 1000. July 500 + August 300 + September 200
    • And so forth etc

    Can this work using the above scenario? If not... what would I need to do to be able to achieve a monthly running total where each month the numbers increment using calculations?

    Out of ideas...

    Many Thanks


    • Edited by Mike Walsh FIN Tuesday, December 13, 2011 10:10 AM ... removed from title
    Wednesday, October 26, 2011 6:05 AM

Answers

All replies

  • You may try using a view. Use the Group By and/or Totals options in the view to provide the calculations. If you are going to have multiple years, you will need to add a year column to keep your information correct.

    -victor

    Please remember to indicate if your question/comment has been answered.

    Wednesday, October 26, 2011 1:38 PM
  • Thanks Vic, thats what I am using now as a work around but was wondering if its possible to do what I have posted above?
    Wednesday, October 26, 2011 11:39 PM
  • Using out of the box functionality, I don't think so. It appears that you can only perform calculations on that particular list item.

    There are some third party tools that can do this. I used CorasWorks webparts in the past. Found the Grid webpart very handy in these situations.

    This is a pretty good resource if you haven't seen it yet.

    http://msdn.microsoft.com/en-us/library/bb862071.aspx

     


    -victor

    Please remember to indicate if your question/comment has been answered.

    Thursday, October 27, 2011 12:55 AM
  • You could add another calculated field called "Quarter", but it's a lot easier to do if the month is a number.

    So you can create the following 2 new calculated columns:

    MonthNum

    =IF(MonthChoice="Jan",1,IF(MonthChoice="Feb",2,IF(MonthChoice="Mar",3,IF(MonthChoice="Apr",4,IF(MonthChoice="May",5,IF(MonthChoice="Jun",6,IF(MonthChoice="Jul",7,IF(MonthChoice="Aug",8,IF(MonthChoice="Sep",9,IF(MonthChoice="Oct",10,IF(MonthChoice="Nov",11,IF(MonthChoice="Dec",12))))))))))))

    Quarter

    =IF(MonthNum>9,4,IF(MonthNum<4,1,IF(MonthNum>6,3,IF(MonthNum>3,2,IF(MonthNum>6,3)))))

    Then, you'd just use grouping and group by quarter and select the Sum of that members column in the Totals section of the view.

     


    Laura Rogers
    SharePoint911: SharePoint Consulting
    Blog: http://www.sharepoint911.com/blogs/laura
    Twitter: WonderLaura
    Books:Beginning SharePoint 2010: Building Business Solutions with SharePoint
    Using InfoPath 2010 with Microsoft SharePoint 2010 Step by Step
    Thursday, October 27, 2011 2:08 AM