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.
7 Comments
Actually, I need all the data comparisons in a single crosstab or list table. With the last five years of data, I would like to compare the data year over year and month over month, using the year filter, Please suggest.
Can you make a date slider where the values are dates in MMMYYYY format rather than the number/character format of the parameter? It would be more convenient for my users to use a slider than enter the date, especially since they may make an error and enter an incorrect date. I am using VA 7.3.
You can make a date slider with the date in MMMYYYY format, but your parameter can be only character or numeric, requiring the approach in the blog. If you are simply using the slider for a filter interaction, for example, with no parameter, the MMMYYYY format works fine.
You can accomplish this with MMYYYY data by creating an aggregated column using a periodic operator. The text from of the expression would be this, if Expenses is the measure and Date is the MMYYYY data item.
RelativePeriod(_Sum_, 'Expenses'n, 'Date'n, _ByMonth_, -1, _Full_, {Date})
You cannot accomplish this in Designer at the Day level--only year and month.
Since the periodic operators do not go down to day level, you would need to pre-process you data in order to make daily comparisons.
Hi Bobbie Wagoner,
Please help me on the same. I really need this solution as anyhow 🙁 . I tried to do it but can not pass the multiple values (Dates) in one single parameter.
I really wanted this logic but this logic applied for only one date comparison.
Actually i need all date comparison in one cross tab or list table