Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Impromptu Current Month/YTD Reporting
- Thread starter dmatty
- Start date
I haven't looked at this forum for some time so I've just seen this. I don't know if you've worked this out but in case you haven't here's an idea for current month.
Create the following calculated variables:
Current_Month: left(date-to-string(today()),7)
Variable_Month: left(date-to-string(ReportDate),7)
This will format both dates as YYYY-MM. If you now set your filter to include Current_Month=Variable_Month , the totals will be for the current month.
YTD can be done also using calculated variables but not easily in the same report (unless you know about sub-reports). The new variable is Start_Of_Year: add-days(today(),-day(ofyear(today())+1) which gives you the first day of the current year. Now the filter should be ReportDate Between today() and Start_of_Year . The calculation obviously assumes that the YTD starts on 1st January and ends 'today'. If it doesn't end 'today' then replace it with a date prompt. This would have the added value of being able to calculate the YTD values from any past date. If the year doesn't start on 1st January a more sophisticated calulation would be needed.
Simon Rouse
Create the following calculated variables:
Current_Month: left(date-to-string(today()),7)
Variable_Month: left(date-to-string(ReportDate),7)
This will format both dates as YYYY-MM. If you now set your filter to include Current_Month=Variable_Month , the totals will be for the current month.
YTD can be done also using calculated variables but not easily in the same report (unless you know about sub-reports). The new variable is Start_Of_Year: add-days(today(),-day(ofyear(today())+1) which gives you the first day of the current year. Now the filter should be ReportDate Between today() and Start_of_Year . The calculation obviously assumes that the YTD starts on 1st January and ends 'today'. If it doesn't end 'today' then replace it with a date prompt. This would have the added value of being able to calculate the YTD values from any past date. If the year doesn't start on 1st January a more sophisticated calulation would be needed.
Simon Rouse
- Status
Similar threads
- Locked
- Question
- Locked
- Question