Year To Date Calculation

[former_member492679]

Hi Guys,

I am writing a report which counts activity in a time period (time period specified by user, Datefrom and DateTo).

I can total the different activities in that time period, however I am struggling to count the total for the year to date, up until the DateTo parameter for the different activities. What I need to do is to calculate from the prior 1st April to the DateTo parameter.

Any guidance would be hugely appreciated.

Many Thanks

OAP

[abhilash_kumar · Accepted solution]

Hi OAP,

When you say 'prior' 1st April, do you mean previous year's 1st April (where the current year is determined by the DateFrom prompt) up until the DateTo prompt's date?

-Abhilash

[former_member492679 · Accepted solution]

Hi Abhilash,

Sorry I mean that if I run the report using the dates 1st September 2015 to 30th September 2015, the YTD calculation should calculate from 1st April 2015 through to 30th September 2015.

Thanks

OAP

[abhilash_kumar · Accepted solution]

Hi OAP,

You'd first need to make that the report's selection formula is setup to return a wider range of records.

Does 1st April indicate start of the Fiscal Year?

-Abhilash

[former_member492679 · Accepted solution]

Hi Abhilash,

Yes that is correct.

Regards

OAP

[abhilash_kumar · Accepted solution]

The first step is to setup the report so that it returns all records starting from the 1st of April up until the DateTo. You'd need to modify the selection formula and it should look something like this:

{Database_date_field} IN [CDate(Year({?DateFromPrompt}) - If(Month({?DateFromPrompt}) < 4 Then 1 else 0), 04, 01) TO {?DateToPrompt}]

You should now be able to insert a summary on a measure field and that should return the total for the current Fiscal YTD.

For other calculations that rely on the Date Range selected, you'd need formulas that look like this:

If {Database_date_field} IN {?DateFromPrompt} TO {?DateToPrompt} then {database_field}

Insert a summary on this formula field and it should return figures for this range alone.

-Abhilash

[former_member492679 · Accepted solution]

Hi Abhilash,

That works brilliant for the YTD, however when I try to use a summary based on the DateFrom To DateTo prompts, it returns the exact same figures as YTD.

I have the report summarizing in Group Footer1. So it looks like:-

                    Month     YTD

Cat1               20          20

Cat2               10          10

Cat3               5             5

Total               35           35

Regards

OAP

[former_member492679 · Accepted solution]

Hi Abhilash,

I have managed to figure it out.

Many thanks for all your help you have been excellent.

Regards

OAP