Date Range Formula - Data from two months ago only
Date Range Formula - Data from two months ago only
(OP)
CR 2016
SQL Server
I'm writing a report will I will have two columns of summary line items of the last two complete months
I'm using lastfullmonth for "last month" or I can also use this:
{date_field} in datevalue(year(currentdate),1,month(currentdate))
to currentdate-day(currentdate)
What I am having trouble with is adjusting the above formula for the complete month two months ago.
I can easily accomplish this by using a crosstab, but I need to have the formulas in a group header, so I'm making a manual crosstab and need the proper formula for dates two months ago.
Any help is appreciated.
Thanks
Ed
SQL Server
I'm writing a report will I will have two columns of summary line items of the last two complete months
I'm using lastfullmonth for "last month" or I can also use this:
{date_field} in datevalue(year(currentdate),1,month(currentdate))
to currentdate-day(currentdate)
What I am having trouble with is adjusting the above formula for the complete month two months ago.
I can easily accomplish this by using a crosstab, but I need to have the formulas in a group header, so I'm making a manual crosstab and need the proper formula for dates two months ago.
Any help is appreciated.
Thanks
Ed
RE: Date Range Formula - Data from two months ago only
Try this:
CODE
Hope this helps.
Cheers,
Pete
RE: Date Range Formula - Data from two months ago only
When I incorporate this into a formula to get an try to get a distinct count of records for the full month (two months ago) I get records for both months in the range.
Here's what I put in the report selection editor using your formula as a model:
{vw_report_event_infos.report_created_date} in [DateSerial(Year(Today()), Month(Today())-2, 1)
to currentdate-day(currentdate)]
I set up this formula to get distinct count for records for the full month of January (being that today is March 1st)
if{vw_report_event_infos.report_created_date} in [DateSerial(Year(Today()), Month(Today())-2, 1)
to DateSerial(Year(Today()), Month(Today())-1, 1) -1]
then distinctcount({vw_report_event_infos.report_id},{vw_report_event_infos.report_type})
RE: Date Range Formula - Data from two months ago only
the if....then part does not change the record set.
I would use an 'accumulation'/'manual running total' formula like below:
CODE -->
RE: Date Range Formula - Data from two months ago only
If the additional grouping is not an option for some reason, a Running Total as follows should also work:
Field to Summarize: {vw_report_event_infos.report_id}
Type of Summary: Distinct Count
Evaluate: Use a Formula:
CODE
I often use manual Running Totals as suggested by fisheromacs. The challenge here is that it will count every record, so if there are duplicate entries (as suggested by the fact you are using a "Distinct Count" rather than a "Count") the result will be inflated. This can certainly be addressed in the code, but I feel that the standard Running Total as above might be simpler.
Hope this helps
Cheers,
Pete