After posting a couple of blogs on the subject of dates and date formats in Visual Analytics Designer, I got a question from a user who wondered how to compare data for a selected date to data from the same day of the previous year. Here’s one way to do this.
The example report enables a user to type in a date value in a variety of formats and displays the sale amount for the specified date, along with the sale amount for the same day of the previous year.
The data source includes information on thousands of orders. Irrelevant data items have been hidden, with the items of interest shown below. Transaction Date has an associated MMDDYYYY format and Transaction Weekday is simply a duplicate of the date with an associated Day of Week format.
A parameter, Param date, is associated with the Parameter role of the Text input field and will store the value typed in the field.
Several calculated data items are created for ‘behind the scenes’ filtering and Ref Date is a data item that will store the date converted from the entered text version of the date.
Note that the ANYDTDTE informat is a great one to use when you are uncertain as to exactly how users will be typing in a date value.
Ref Date (Yr-1) is the ‘same day a year ago’ date.
A filter on the list table completes the report:
( Ref Date = Transaction Date ) OR ( Ref Date (Yr-1) = Transaction Date )
The data items below are now used to populate the report showing the data for the specified date for comparison with the data for the same day of the previous year.
With the addition of a few more calculated data items and filters, some additional report objects can offer alternate ways of displaying the data.
Prod Sale (ref date) and Prod Sale (Yr-1) are calculated as below:
The addition of the two new data items allow the information to be presented in the list table below, with this filter applied: (Prod Sale (ref date) NotMissing OR Prod Sale (Yr-1) NotMissing)
The same filter (Prod Sale (ref date) NotMissing OR Prod Sale (Yr-1) NotMissing) can be applied to a crosstab object to produce the result below:
The addition of one additional calculated data item, Date, and a new crosstab object with a filter on Date, enables still a different display.
I found this to be an interesting example–both the problem and the solutions. I hope this blog will give you more ideas about using your dates to the best advantage in report.