I recently set up a dashboard for one of our clients. The brief was to create a chart which showed the time series along with the current and last month values along with the value from the same month last year for comparison.
After a small amount of experimentation I created a calculated field in the view which worked out the date as it was a month ago and the same for a year ago – this was the basis for creating the datasets we needed. We then used report sub-queries to pull out the right data for each offset.
PART 1 – Offset Dates in the view
Before you get started you will need to make sure you have the right permissions to edit your views in Yellowfin. There’s a couple of important things to note before you get started:
- I’d suggest cloning your views to make sure you can roll-back if you have any issues.
- Calculated fields on large datasets can utilise a large amount of resource to generate so be cautious that you’re not applying a calculated field to a significant dataset.
- Go to Admin Console within Yellowfin.
- Find the view you want to add the offset date to and edit the view.
- Move straight through to the second screen where you define your fields:
- Expand ‘Calculated Fields’ > ‘Freehand SQL’ and drag in the Dimension
- Double click the new field and set-up it’s name (e.g. Last Month), description and mark it as Active.
- Click the formula tab and add in cast(DATEADD(month, -1, <DATE_FIELD>) as date)
The value <DATE_FIELD> should be replaced by the name of your date field.
- Click the format tab and set it up the same as your date field.
- Click Activate
- Do the same for Last Year using the formula cast(DATEADD(year, -1, <DATE_FIELD>) as date)
- Move through to the last screen for editing your view by clicking on 3 then Save or Activate depending on your preference.
PART 2 – Setting up the report
The next part is to create a new report and join the data using the offset dates. This is done with a sub-query.
- Create a new report in Yellowfin, using the view you’ve just amended
- Drag in the fields you want for your first report along with the offset date fields e.g. ‘Last Month’. I suggest a quick validation to make sure the dates you see in the table are displaying what you’d expect.
- Click the plus at the left side of the screen to set-up a sub-query.
- Leave the setting as Append and click Advanced. Choose the same view as your main dataset.
- Join on the ‘Last Month’ field from the main dataset equal to <DATE_FIELD> from the joined dataset.
- Repeat this for each offset value you’re after.
- Drag in the fields for the values you want to show and you’re all done, ready to create the charts with those offset values.
Keep exploring! Daniel.