on 04-14-2016 2:28 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
78 | |
10 | |
9 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.