Reporting Aging using 3 fomulas per month.

[frankee_gee]

Hoping someone can help with reducing formulas

In an Aging report I have the following formulas in a table for each month of this year. I don't believe this to be efficient to much longer. This goes all from Jan to Present # columns by December I will have 3 columns by 12 months = 36 columns.

Column Name: Formula
NovErects:
=--(AND([@ErectDate]<=DATE(2014,11,30),OR([@DismantleDate]="",[@DismantleDate]>=DATE(2014,11,30))))
[TABLE="width: 166"]
<colgroup><col></colgroup><tbody>[TR]
[TD]
NovErectDays:
[/TD]
[/TR]
</tbody>[/TABLE]
=IF([@NovErects]=1,DATE(2014,11,30)-[@ErectDate],0)

[TABLE="width: 166"]
<colgroup><col></colgroup><tbody>[TR]
[TD]NovAging:
[/TD]
[/TR]
</tbody>[/TABLE]
=IF([@NovErects]=1,IF([@NovErectDays]<90,"Less than 90 days",IF([@NovErectDays]<121,"90-120 days",IF([@NovErectDays]<181,"120-180 days",IF([@NovErectDays]<99999,"> 180 days")))))

1. Count of items still on rent per month.
2. Number of days each item has been out on rent for that month.
3. Aging buckets: Count of items that fall into the ; 90 days on rent, 120 days on rent and so on.

using this in a table and pivot table...(connected to power pivot mostly using a calender)

Thanks in advance,

Frankee.

 

[Andrew Poulsom]

This single formula seems to do the job of all three:

=IF(([@ErectDate]<=DATE(2014,11,30))*(([@DismantleDate]="")+([@DismantleDate]>=DATE(2014,11,30))),LOOKUP(DATE(2014,11,30)-[@ErectDate],{0,90,121,181},{"Less than 90 days","90-120 days","120-180 days","> 180 days"}))

 

[frankee_gee]

Andrew,

Thank you...this worked. We eliminated the need for 3 columns per month. This would bring me to only on column per month. I tested this by placing the new field with your formula ; column labeled: "NovTest". in the pivot table copied one to the Rows and the other to Values.

Can anything be done where this whole thing calculates without having to add columns to the table it self...just thinking down the line when in 4 years...it will be 12 columns X 4 years...48 columns.

I hope it's not to much...just would like to drop a calculated field and have it populate.


Thank for your help...this is a big help,

Frankee.

 

[Andrew Poulsom]

Given that you want a field for each month/year I don't know how you could use a calculated field, sorry.