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

Hi Dillon,

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

Regards,

Mike

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

Hi Dillong,

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

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

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

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

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:

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:

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

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

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

Charts Setup:

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

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:

And altering your CF to be something like:

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

25, plus filtered by another Dimension:

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

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

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

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

Hi Team,

Has this enhancement request been considered? Just wanted to understand the timelines.

Thanks,

Anjali

Hi Anjali,

No updates to report on this particular task at this time; I've gone ahead and added your organization as impacted for additional visibility; you can also request an escalation through your CSM if needed, task ID here is YFN-21697.

Thanks,
Eric

Hi Community,

Just found the ability to add colours to a line chart with multiple metrics. Not sure if this what you are looking for. Go to Series configuration, enable custom color and select desired color.

Regards,

Sai

Hi All,

Just wanted to follow up formally to see if this was a solution for anyone; the created task here remains unresolved but this may have been addressed elsewhere, and may be a viable solution for some.

Thanks,

Eric

Hi Anjali,

Our product team was specifically wondering if adding the references as subqueries is a viable resolution in your circumstance. If not, please document your specific use case and desired functionality to proceed with this request.

Thanks,
Eric

Hi Eric,

Sai (who had commented here with a solution) is from my team and he was able to find the mentioned solution for the problem we were having. So the problem stands resolved for our particular use case. Thanks for following-up.

Regards,

Anjali

Hi Anjali,

Thanks for the update, glad things are working for your organization at this time. I'll mark this question as Completed as such, feel welcome to reach out in the future.

Thanks,

Eric