Solved

# Create a non-calendar date (year) in view

Posted on 2007-08-10
222 Views
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
Question by:AliciaVee

LVL 22

Expert Comment

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

LVL 31

Accepted Solution

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

LVL 22

Expert Comment

Nice and concise!  Give this one to qwaletee!
0

Author Comment

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

Author Comment

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

LVL 46

Assisted Solution

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

Author Comment

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

Author Comment

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

LVL 46

Expert Comment

Bummer... Forgot to remove the additional "s" in the neater formula... :-\$

Thanks!
0

LVL 31

Expert Comment

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

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…