I was recently tasked with creating a chart in Yellowfin with a graph at the top and a couple of charts underneath showing the current value and the value from last month. This meant we needed different datasets for each chart within the same report.
I started by setting up a column in our data that showed the date minus 1 month, you can either do this in your source data or a calculated field in the view depending on your preference.
The report was made up of a time series ‘Area’ chart at the top with a couple of ‘Numeric Display’ charts underneath. I used a single filter to display the right data for each chart.
The objective was to have everything before the filter date to show on the Area chart with the Numeric Display charts just showing the number relevant to them – the Numeric Display sums all values in the field you choose so we needed to restrict it to just one value.
Here’s the steps:
- Create a new chart in Yellowfin and bring in the data you want to use including a unique identifier you can join to.
- Add the main filter into the filter area:
NOTE: Sub-queries will be filtered by anything you filter in your main dataset. If you want to have two different datasets that don’t overlap, then I’d suggest not filtering your main dataset and joining back to itself for each chart you want to create.
- Click the + to add a sub-query on the left of the report screen.
- Join the data back to itself using the unique ID. In my example I joined the date field of the new dataset to the ‘Previous Month’ date in my joined data.
NOTE: If you have a large dataset then this can use a considerable amount of resource to do the join so be cautious when joining datasets.Whilst adding the join, drag in the field you want to filter the second dataset to the ‘sub-query filter’ box.
- On your joined dataset hover over the filter for your sub-query (should be the one not a lighter grey) and click ‘Advanced Settings’. It should look similar to this:
- Choose ‘Link to Filter’ then click ‘Define Link’ and set it up as you want it. For my scenario I had the original filter set to ‘Less than or Equal to’ and for my second filter I specified the value where it was ‘Equal’
- Once your data’s been set-up, move on to the charts tab and set up the charts you want before moving on to your output to set-up your multi-chart canvas.
Another great little feature in Yellowfin.
Keep exploring! Daniel.