Select Page

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.
  1. Go to Admin Console within Yellowfin.
  2. Find the view you want to add the offset date to and edit the view.
  3. Move straight through to the second screen where you define your fields:
    Yellowfin Edit View Toolbar
  4. Expand ‘Calculated Fields’ > ‘Freehand SQL’ and drag in the Dimension
  5. Double click the new field and set-up it’s name (e.g. Last Month), description and mark it as Active.
  6. 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.
  7. Click the format tab and set it up the same as your date field.
  8. Click Activate
  9. Do the same for Last Year using the formula cast(DATEADD(year, -1, <DATE_FIELD>) as date)
  10. 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.

  1. Create a new report in Yellowfin, using the view you’ve just amended
  2. 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.
  3. Click the plus at the left side of the screen to set-up a sub-query.
    Yellowfin Plus Icon
  4. Leave the setting as Append and click Advanced. Choose the same view as your main dataset.
  5. Join on the ‘Last Month’ field from the main dataset equal to <DATE_FIELD> from the joined dataset.
  6. Repeat this for each offset value you’re after.
  7. 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.

%d bloggers like this: