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??
AliciaVeeAsked:
Who is Participating?
 
qwaleteeConnect With a Mentor Commented:
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:
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
 
Bill-HansonCommented:
Nice and concise!  Give this one to qwaletee!
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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 BosmanConnect With a Mentor Groupware 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.