Add ability to color a Line Chart with multiple metrics in the vertical axis

Dillon Hoefener shared this idea 9 months ago
Idea Logged

Hi,

I have a simple report that includes data for multiple regions with a metric value (see screenshots). This report includes 2 columns for targets (yellow threshold and red threshold). I would like to include the multiple regions as shown in screenshot 2 AND the target lines defined in the threshold columns, but none of the line chart options (line, combination) allow me to bring in the threshold series with the primary metric series because there are multiple regions (where region is used as the color in the chart to display multiple regions).

I see the ability to add reference lines with a hardcoded value, but the values for the targets need to be dynamic because they are calculated based on the data in the source table, so this does not work.

This is a very common use case to display targets on a chart along with data for more than one dimension series so I have to assume this is somehow possible in Yellowfin so if anyone can provide guidance on how I can set this up I would appreciate it.

If this is not possible, I would request that reference line values allow for binding to a column value in the report be added in a future release.

Thanks

Comments (10)

photo
1

Hi Dillon,


Thanks for your question.


You should be able to create this type of Chart, however, you will need to restructure your data into a long format before you do this. Apologies if I misunderstand your data slightly, however, you should be able to get the idea and adjust accordingly.


To do this, you would create 2 Union Subqueries, with a Calculated Field in the Master Query called 'KPI Value' and then the name 'Yellow Threshold' and 'Red Threshold' on the additional Subqueries that match the Calculated Field column that you created. You would then join KPI Value to Yellow Threshold, and KPI Value to Red Threshold.


The result should be a single column with all your metrics, with a categorical column that you can use in the Chart to separate via colours.


Let me know if this gets the result that you were looking for. Alternatively, you could use a mixture of the method above, with a Combination Line Chart.


Kind regards,

Simon

photo
1

Hi Dillon,

I just wanted to check in and see how things are going with this.

Regards,

Mike

photo
1

Hi Mike,

I'm having an issue with this on my first pass but only because of the SQL inflexibility of the Druid db I'm using. I expect this method will work though when I deal with the db error.

I knew this would be an approach I could take, but it's definitely less than ideal. Also, don't reports with unions remove the drill down capability?

I think it would be very helpful to be able to have dynamic reference lines based on data in the report. Especially in the case where one would want to calculate the target based on past performance or forecasted data, which I think is a pretty common approach to setting business targets. Do you think this is something that could be added to a future release?

Thanks

photo
1

Hi Dillong,

Thanks for your response. Are you looking for Reference Lines on Metric data? Something like this:

/51d3a212a9c25cb3d04f61d2dbcbd50a

Which you can see I manually added Line Shape Widgets for the horizontal axis on the Date field, which would be my recommended means of accomplishing this for the time being.

Although, in terms of implementation, my concern here would be coming up with a logical way to dynamically assign Reference Lines on non-Metric values universally within the application. For example, if I wanted Reference Lines on 2014, 2016, 2017, how would Yellowfin know automatically those are the desired Reference Line's?

Or... is your idea just to have dynamic Reference Lines on Metrics? I think it may be the latter, but I'd just like to clarify. If it is this option, I'm struggling a bit to see how the logic would work for this, so I suppose my question is, what would the dynamic value be based on? Or what could it be based on? Can you provide an example of a small data set where based on a certain parameter you get x as a dynamic result? For example, in your multiple series...2.jpg image, can you manually draw a line where you'd like to see Reference Line's and where you'd ideally be pulling that Reference Line from automatically?

Thanks,

Mike

photo
1

Hi Mike,

I'm envisioning the values for the line to come directly from the report dataset, where the dates/years may have different values across the timeline, since it is very unlikely target values would remain unchanged for multiple years.

In my example, the yellow threshold at 70% and red threshold at 90% (screenshot 1) would extend across the entire date range/x-axis at those values, but because those values are different based on conditions, I need them to come from the calculated fields for these in the report instead of hardcoding the values as reference lines. So ultimately, I'm looking for at least 1 of 2 methods to be available in Yellowfin:

1. Given how the chart builder functions today, I would expect it to allow me to drag in the target columns along with the metric value even when I have a dimension in the color field to display multiple series such as one series per region (screenshot 2). The use case is that all regions not meeting these targets are underperforming and I need to be able to visualize that on the same chart to clearly see which regions are dragging down overall company performance relative to the other regions.

2. If for some reason method 1 isn't doable like it isn't today, in my example I would expect to be able to set reference lines on the "Yellow Threshold" and "Red Threshold" columns in the report. This would result in essentially the same behavior as method 1 where Yellowfin would know what the x-axis values (dates) are for the chart based on the chart builder columns used in the chart and it would match up the y-axis threshold values (reference lines bound to the threshold column names) with the corresponding dates in the report. Put simply, by tying a reference line to a report column name, I'm asking Yellowfin to go set reference lines at the values of that column in the report for the entire dataset horizon.

In a theoretical example that I think applies to many businesses, I may show multiple years in the report like in your example where I might calculate targets for each year based on the previous year's results (i.e. revenue target should be 30% higher in 2021 than 2020's actual revenue), so I would want my target line to be variable based on performance, and therefore the result of some expression/calculation that I've defined in a report calculated field.

It doesn't really matter which option would be implemented to meet this need; it's just that the high-level request is to be able to always use targets based on calculated fields in the report. Make sense?

Thanks

photo
1

Hi Dillon,

Thanks for the additional feedback. I've reached out to our Consulting Team to see if they have any additional thoughts on this before I submit an enhancement request.

I hope to have another update for you soon.

Regards,

Mike

photo
1

Hi Dillon,

I discussed this with a Consultant and came up with 2 potential options.

The first one:

1. Create a calculated field in your report that will act as a dummy, it can just contain the value '1'

2. Add an append sub-query

3. While configuring the join, create a second calculated field in your sub-query, also called '1', so you can join on it:

/0b5ca5869c2cd967eeeb47592cc4ec90

I also chose Agency Type as a filter value, so I can filter the result set (thus making the threshold line dynamic).

4. Bring the same dimension as a filter, Agency Type in my example, into the master query as well.

5. Link the filters like so:

/420523a4203aee5ece9b88a1c09a5bc4

Then create a CF that takes the values of your two metrics (you may need to use the average aggregation)

/c7e667378e3d3c2bbf595299ba73af21

Final setup (I hid the '1' CF in master query):

/b51c75e6c8686d9dbd7fde9f5520a2e9

As you can see, there's one value for the CF result, meaning we can use it as a threshold.

Charts Setup:

/cac4b64dbd29e4726735f88f6e75175d

Now I can filter this in the Report Itself by my Dimension:

/5ce9bfa22737605c7393e7b40bfad0bc

You can see the result is different from what was in the Charts section because now I'm filtering by Agency Type: Online.


The second option would be to reference a parameter in the Calculated Field, which would also allow for some direct user input.


You can do this by pulling Parameter 1 into main query section:

/27a741f02436d9cd43e7317933f5682d

And altering your CF to be something like:

/125cf73b4e1d508a50aa5d4cf55d95a3

So now you can input the value you're looking to, in this case, multiply by:

/5c7b692bda3d52605bdb98f85a57dbef

25, plus filtered by another Dimension:

/1c2f1ea7350eb05f4fdcc2e2ec5a5642

Hopefully one of these options are suitable, or triggers thoughts on a potential solution.

Please let me know your thoughts on this and how goes.

Regards,

Mike

photo
1

Hi Mike,

I followed your steps to set up the report this way, but I have the same core issue as I did before: I cannot display more than one series in the chart with the target line. In your example, what I need to be able to do is see a line for each of your agency types in the chart for some metric value and the target line.

Is this possible?

Thanks

photo
1

Hi Dillon,

You would basically repeat the steps for the first threshold, by creating additional sub-queries. I think we can get this sorted in a quick (probably less than 30 minute call). Your Account Manager will be reaching out shortly to discuss availability so all the appropriate parties can look at this together (Support doesn't typically handle report building questions). She should be in touch shortly.

Regards,

Mike

photo
1

Hi Dillon,

Thanks for joining the call. The method we provided still requires some user input, or knowledge of the data beforehand, so ultimately, the request is to be able to color a Line Chart with multiple metrics in the vertical axis, which will require an enhancement.

As such, I've gone ahead and converted this to an Idea item and submitted an internal enhancement request. Any potential updates surrounding this will be posted here.

Regards,

Mike