Formula for current date calculations 1

[nb4884]

Hi All,

I have data for a project as below:
Start of the month(date) Monthly Usage(data in seconds)
03/01/2011 2000
04/01/2011 1000
05/01/2011 3000
and so on
10/01/2011 2000
11/01/2011 3000

I have to calculate the usage on fiscal year i.e (04/01/2011- 03/31/2012 is FY 2012)
Please suggest me a formula that would calculate the total Usage for ongoing FY till currentdat.
That is a formula that would sum up all the usage from 04/01/2011 to currentdate. i.e 1k+3K..+2K+ portion of 3K till currentdate.

Thanks

 

[Madawc]

Create a formula field for Fiscal year: you can do this by something like

if Month({your.date}) < 4 
then Year({your.date}) -1
else Year({your.date})

Now you can group and total using Fiscal Year, or do a crosstab.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[nb4884]

Thanks Madawc,

If I create a formula for fiscal year as above, how would I break 3000 which is in month 11/01/2011 to calculate until tody's date.

3000 is for whole month. But I want fraction of it until today's date.

 

[lbass]

So the 3000 is the projected usage for the month of November?

if year({table.date}+275) = 2012 and
{table.date} <= currentdate then
(
if month({table.date}) <> month(currentdate) then
{table.monthlyusage} else
({table.monthlyusage}/day(dateserial(date(year(currentdate), month(currentdate)+1,1)-1))*day(currentdate)
)

-LB

 

[nb4884]

Thanks LB yes 3000 is the projected usage for Nov.

I tried creating the formula but it gives and error ) is missing.

I then added a closing bracket )

but then there was an error" Too many arguments have been ..."

and highlighted "(dateserial(date(year(currentdate), month(currentdate)+1,1)-1))"

Pls suggest what is missing

 

[lbass]

if year({table.date}+275) = 2012 and
{table.date} <= currentdate then
(
if month({table.date}) <> month(currentdate) then
{table.monthlyusage} else
(
{table.monthlyusage}/day(dateserial(year(currentdate), month(currentdate)+1,1)-1)
)*day(currentdate)
)


-LB

 

[nb4884]

LB, the formula doesnot show any error.

My FY logic got changed, can i use same formula if I were to calculate usage from 07/01 - 06/31 ?

Thanks

 

[lbass]

Change the 275 to 184.

-LB

 

[nb4884]

Thanks LB, and next year will I have to mamually update the year to2013?

Is there a way to make the formula general for all Years.

Many Thanks!

 

[lbass]

I think you should create a number parameter {?Year} and use that instead of 2012 in the formula.

-LB

 

[nb4884]

So instead of 2012...cant I write something like year(currentdate)+1.

Adding parameter is something not in scope of the report design reqt.

Thanks

 

[lbass]

No, because the fiscal year is not always the same as Year(currentdate)+1.

-LB

 

[lbass]

I guess you could use the following instead of 2012:

year(currentdate+184)

-LB

 

[nb4884]

Thanks LB, that formula works.
Can you also suggest one more formula, I could have opened another thread but it is a similar one.pretty please?

Second scenario is calculate monthly usage starting from cuuenrdate upto before 07/01(0r 06/30 but the dates I have are starting from 01 of every month.)

Does this look right?

if year({table.date}+184) = year(currentdate+184) and
{table.date} > currentdate then
(
if month({table.date}) <> month(currentdate) then
{table.monthlyusage} else
({table.monthlyusage}/day(dateserial(date(year(currentdate), month(currentdate)+1,1)-1))*day(currentdate)
)

Thanks a lot!!!

 

[lbass]

Try the following, but you should make sure the results for the current month total correctly when you look at the subtotals for the current month returned by each of the two formulas.

if year({table.date}+184) = year(currentdate+184) and
{table.date} > currentdate then
(
if month({table.date}) <> month(currentdate) then
{table.monthlyusage} else
({table.monthlyusage}/day(dateserial(date(year(currentdate), month(currentdate)+1,1)-1))*(day(dateserial(date(year(currentdate), month(currentdate)+1,1)-1))-day(currentdate))
)

-LB

 

[nb4884]

Thanks LB ,
I get the same error ) is missing. And when I added ) i got an error"Too many Arguements..." highlighting "dateserial(date(year(currentdate), month(currentdate)+1,1)-1"

Please note that this reverse formula for calculating from todays date to 6/30 or 07/01 is for a different date table.date2 and different usage as table.monthlyusage2

Otherwise the data is almost the same ie starting on 1st of every month , there is some usage until unlimited months. (same as scenario 1)


Thanks

 

[lbass]

if year({table.date}+184) = year(currentdate+184) and
{table.date} > currentdate then
(
if month({table.date}) <> month(currentdate) then
{table.monthlyusage} else
({table.monthlyusage}/day(dateserial(date(year(currentdate), month(currentdate)+1,1)-1))*(day(dateserial(date(year(currentdate), month(currentdate)+1,1)-1)-day(currentdate))
)


-LB

 

[nb4884]

Thanks LB,

The formula appears to be the same as one above (15 Nov 11 17:00)

I am getting ) missing error.

Added a ) but then got an error as "not too many ..."

Pls suggest

 

[lbass]

Sorry, try this:

if year({table.date}+184) = year(currentdate+184) and
{table.date} > currentdate then
(
if month({table.date}) <> month(currentdate) then
{table.monthlyusage} else
(
{table.monthlyusage}/
day(
dateserial(year(currentdate), month(currentdate)+1,1)-1
)*
(
day
(
dateserial(year(currentdate), month(currentdate)+1,1)-1
)-day(currentdate)
)
)
)

-LB

 

[nb4884]

Thanks LB, but the formula does not pick the data for the remaining month of Nov. (Like in the 1st formula it ends calculating for Nov- averaged on remaining days)

It does not average on Nov i.e starting from currentdate.

It starts calculating only from 12/01 onwards. Although it works on the end, where it calculates the data ending on 6/30 or before 07/01.