Draw a line on the visual based on filter value

@shaun I have a requirement where its required to draw a line on the visual based on the filter’s value. Can you please suggest me how this can be achieved ?

@Chaish what sort of visual are you trying to place a line on? and can you provide a bit more detail about the “filter value”?

I am imagining a threshold line of sorts that you want to display on a bar or line chart, is that your use-case?

@shaun The visual I m considering here is lines chart. Based on the below screenshots, the requirement says that to draw a dashed line with the text KPI Threshold = 31 when the KPI named K1 is selected which has the value 31. Can you please suggest.


@Chaish by placing a new element in the color shelf of your line visual you will get a new line. Here’s an example:

In your case you want to set that threshold more dynamically. Here’s one method to achieve this. It uses a custom input box whereby the user can manually enter the threshold they are interested in.

It’s more difficult to do using the filter drop down list.

2 Likes

@shaun Thanks a lot for coming up with the alternate solutions. But my requirement is more related to using filter drop down list to achieve this which you have mentioned more difficult to implement. I do understand.

@Chaish where is that filter drop down coming from? is that a custom filter or is that coming from a dataset field?

If you use a single select drop down (instead of multi select) it might be possible to do.

@shaun Sorry for the delay in response. Filter drop down is coming from Dataset field and it will be single select only. Can you please suggest me how it can be done if the drop down is single select ?

Best Regards,
Chaithra

I would first recommend reviewing this how-to video on parameters:

We can then use the concept of parameters in our shelf expressions to have the threshold line drawn dynamically.

max (<<threshold>>)

The “threshold” parameter is set by the filter widget, which in turn is used by the visual.

3 Likes

@shaun Is it not possible to achieve the above with Lines graph ? If I select Lines graph I don’t see any field called “Line Measure” where max(<<field_name>>) can be entered.

Best Regards,
Chaithra

@Chaish You want to make sure to use “combo chart”

image

You can also accomplish this with line chart, but you want to place the “max (<<…>>)” in the color shelf

1 Like

@shaun I tried the above one but it looks like it takes the max of the threshold value. As per the requirement, it should be like when I select a KPI in the filter, the threshold value for that KPI should be automatically selected and a line should be drawn on the Lines graph. The line which is drawn will be based on the KPI threshold for a particular KPI and user will not explicitly select any Threshold value as it is shown in the earlier answer for my query. Please look at the below attachment.

When I Tried the solution given by you, I get the below graph where it takes the maximum value.

Can we achieve this using filters only ?

Best Regards,
Chaithra

@Chaish I think you can accomplish this using if condition & window function. Below is an example.

In your example I think you could probably use something like this:

max (max (if(KPI=<<kpi>>, KPI_threshold, 0))) over ()

You will probably have to tweak this for your data model & setup but hopefully it points you in the right direction.

1 Like

@shaun I tried all the things suggested by you. But its not actually related to the requirement I have. Using parameter concept user has to explicitly select the value to draw a line but the actual requirement needs to automatically populate the value of KPI Threshold based on the KPI value and draw the line accordingly. Can you please suggest me for this case ?

@Chaish do you have a lookup table that determines the KPI threshold based on KPI value? if yes, then if you make selection on KPI and use that in the IF statement expression I mentioned it should work.

max (max (if(KPI=<<kpi>>, KPI_threshold, 0))) over ()

@shaun Thanks for the suggestion. Yeah we will be receiving a hive table with KPI and KPI Threshold value. But I need to apply dependency between them while adding them as filters to the dashboard. Let me try the way you have said and will get back to you.

Best Regards,
Chaithra