• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Create a non-calendar date (year) in view

Experts,
I need to track sales from a specific period that does not follow a normal calendar year.  In my documents I will have a date, eg. 4/1/2006 or 3/31/2007

The reporting year needs to be 4/1/xx through 3/31/xx  -- this spans two years.

In my view, I'd like to list the year first eg. 2006 and total fields from documents that include a sales date from 4/1/2006 through 3/31/2007 and then the next category in the view will be 2007, and these will total documents from 4/1/2007 through 3/31/2008.

Can this be done??
0
AliciaVee
Asked:
AliciaVee
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
Bill-HansonCommented:
Sure.

You need a new computed field on your form called "ReportingPeriod".  This is the field that you will categorize your view on.

Here is the formula.  Just substitute your date field name on the first line:

SourceDate := YourDateFieldName;
SourceYear := @Year(SourceDate);
TestPeriodStart := @Date(2000; 4; 1);
TestDate := @Date(2000; @Month(SourceDate); @Day(SourceDate));
@If(TestDate - TestPeriodStart < 0; SourceYear-1; SourceYear);
0
 
qwaleteeCommented:
Or, more pithy:

@Year(SalesDate) - (@Month(SalesDate)<4)

This says to check if the month of the field SalesDate is less than April (i.e., Jan 1 to Mar 31).  if so, subtract one from the year, otherwise leave it as is.

That's because @Month(SalesDate)<4 evalues to @True for the first three months, @False for the remaining months... and @True is 1, @False is zero.  So we're subtracting either 1 or 0.

Simple, no?
0
 
Bill-HansonCommented:
Nice and concise!  Give this one to qwaletee!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AliciaVeeAuthor Commented:
Ah -- I even understand this code!  Cool!  I'll check it in an hour or so (on my train ride home) and then log on back later tonight to verify it works.
0
 
AliciaVeeAuthor Commented:
Not sure what I missed -- but I am getting the following error, when I try to open the document:
Field: RptStartDate : Incorrect Data Type For Operator or @Function: Time/Date Expected

The field RptStartDate is my "new" field where I have placed the following code, and is where I want to display my reporting year:
@Year(DateFirstPlaced) - (@Month(DateFirstPlaceds)<4)

I have RptStartDate set as computed, with a type of Date/Time
0
 
Sjef BosmanGroupware ConsultantCommented:
Hello AliciaVee,

There's a typo in your code, an "s" too much:
      @Year(DateFirstPlaced) - (@Month(DateFirstPlaced)<4)

IMHO, neater and less cryptic would be
      @Year(DateFirstPlaced) - @If(@Month(DateFirstPlaceds)<4; 1; 0)

No points please for the formula...

Regards,
   Sjef
0
 
AliciaVeeAuthor Commented:
sjef,
Darn -- I feel like a big dummy.  The basics of coding -- check the syntax!  Ha ha.  Got to laugh at myself.

Thanks.  That fixed it.  
0
 
AliciaVeeAuthor Commented:
Sjef,
I felt the need to give you at least 100 points -- because I winded up using your "neater" code -- so if someone where to pull this solution up and only view the other solution post -- it wasn't what I winded up using.  Thanks again.
0
 
Sjef BosmanGroupware ConsultantCommented:
Bummer... Forgot to remove the additional "s" in the neater formula... :-$

Thanks!
0
 
qwaleteeCommented:
A little easier to understand perhaps, but neater?
subtractOne_If_LateInPreviousFiscalYear := @If(@Month(SalesDate)<4;1;0);
@Year(SalesDate) - subtractOne_If_LateInPreviousFiscalYear;

But that @If is still ugly, so this is both, because t he cryptic part is self documented by the variable name:
subtractOne_If_LateInPreviousFiscalYear := @Month(SalesDate)<4;
@Year(SalesDate) - subtractOne_If_LateInPreviousFiscalYear;
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now