Hiding lines in a Line chart

[#1]

Hello ,

     I'm developing a new qlikview app in version 9.  The requirement is to display day number in X axis dimension and

We need to display chart for current year, current year -1, current year -2 and current year -3.  E.g: 2011, 2010,2009 and 2008.

For each year we have 3 types metrics.

1) actual

2) planned

5) forecast

So, each year on the chart will have 3 lines. That makes total of 12 lines on the chart.

Now the requirement is to have user to be able to select and de-select the lines on the chart. By default app shoul display only actuals and if required user can add more lines on the chart.  We are thinking to use variables. Setting the variables depending on user selection and using this variables to conditionally display or hide the lines on the chart. But, not sure how to implement this.

Let me know how can this be implemented in qlikview. Any help on this is greatly appreciated. Thanks in advance.

[#2]

As far as I know there is no way to hide individual rows of a chart, though I believe in version 11 they is the ability to conditionally display fields.

In the past how I have gotten around it is to conditionally show the entire chart.

In my load script I will add the following script:

SET

HidePrefix='_' RevenueGroup:

LOAD * INLINE [
_RevenueGroup
Actual
Planned

Forecast

Actual vs Planned

Planned vs Forecast

ALL

];

Then I will add the _RevnueGroup to a listbox, on the presentation tab I change the 'Selection Style Override' to LED, close the properties box, make a selection within the listbox and open the properties again and select 'Always One Selected Value'.

Now on the 1st chart, go to the layout tab, select Conditional under Show and put _RevenueGroup = 'Actual' as the expression.

The downside here is you need to maintain multiple charts but the user will have an interactive means to look at the charts/data they want to see.

[johnw]

I've conditionally hidden lines, allowing users to select or deselect them.  In one of my applications for example, by default I display only the average value of a something for each month.  So you see a single trend line.  Then I have a checkbox that says "Show Distribution".  If you check it, the chart will display the following percentiles: 95th, 75th, median, 25th and 5th.  It's busy and more confusing to some users that way, but useful for the statistically minded.  It's done with a single chart.  Here's an example expression from the chart:

if(getselectedcount("Show Distribution"),fractile("Test %",.95))

The hiding, then, is done by simply calculating null for the expression when we want it hidden.

The "Show Distribution" field only has a single possible value, 'Show Distribution'.  It is either selected or it isn't.  The checkbox is just a list box with the windows checkboxes selection style override from the Presentation tab.  In your case, you'd probably handle it with two values for a field, and then checking if the value corresponding to a particular line is selected.

[#4]

Thank a lot Guys!! Both the answers are much helpful. 

In my case I ahve 4 years and 3 metrics for each year, that makes 12 choices for users. Do you have any suggestions on what onject to use in order to present them to users. I was thinking Matrix kind of representaion would make sense. but not sure how to implement. Please advice.

[johnw · Accepted solution]

And there's no interaction between choices, so a user could, say, look at the actual data for 2010 compared to the planned data for 2008, and skip all other metrics for all other years?

You could probably handle it with a regular list box.  Have your values like this:

2008 Actual
2008 Forecast
2008 Planned
2009 Actual
etc.

Then remove the single column checkbox to get multiple columns, give it room for one column for each year, sort auto-ascending by state and text A->Z.  As long as there's room for everything, I think they would then stay in a nice, disciplined matrix when selected.

Edit: And then your expressions might look something like this:

if(index(getfieldselections("Lines to Show")
        ,"Current Year"-1 & ' Actual')
  ,sum({<Year={'$(="Current Year"-1)'}>} "Actual Something"))

Edit: And I'm drifting off topic, but I'd probably make actual, forecast and planned numbers very different colors (preferably distinguishable by the color blind), and then make them lighter and lighter versions of that color the further back in time you go.  So that a printed version still makes sense, I'd also include distinct symbols either for everything, for for just actual, forecast and planned if the greyscale version of each year is distinct enough from the other years.

[#6]

Hi John,

  I made changes as you suggested and the app is now working. but, when user selects more than 8 selections (out of 12) from the list GetFieldSelections() returns values not selected with NOT keyword Instead of returing selected values. This makes Charts to display lines for non-selected values and hide selected values.

Please advice.

[johnw]

Ah, sorry.  I should have thought of that.  You need an additional parameter to tell QlikView not to collapse the list like that.

getfieldselections("Lines to Show",'',999)

The second parameter is a separator, which doesn't matter for us, but we can't leave it missing.  The last parameter is how many individual values to list before it starts collapsing the list.  You don't need 999, but the idea is to put in something arbitrarily high so that you never need to worry about it again.

[#8]

Hi, can I possibly get the qlikview file to take it as an example?

I'm trying to do something similar to this but is not working.

Thanks

[#9]

In this method, is there a way to get the CheckBox to default to a selected state after a reload?

[johnw]

So you have a list box that you've made look like checkboxes, and you want some but not all of the options checked when the application comes up? One simple way is to just check the right ones before saving. But I don't tend to trust that approach. I won't remember to set everything perfectly every time I save, and definitely don't expect other programmers to remember the defaults I wanted to have.

I'm out of the loop these days as far as the current best practices, but I would set a triggered action. Settings, document properties, triggers, OnOpen, add action, add. From there you have a couple options. Best if it works is probably selection, select in field, put in your field, then write a search string like this:

("Apple"|"Orange"|"Pear")

But last time I needed to do this, I ended up writing a macro for my default chart-controlling checkboxes.

sub Default
    set field = activedocument.fields("Dimensions")
    field.select "Coil In"
    field.toggleselect "Coil Out / Delay #"
    field.toggleselect "Date Produced"
    field.toggleselect "Defect / Delay Reason"
    field.toggleselect "Disposition"
    field.toggleselect "Facility"
    field.toggleselect "Final Result"
    field.toggleselect "Gauge In"
    field.toggleselect "Gauge Out"
    field.toggleselect "Product"
    field.toggleselect "Steel Grade"
    field.toggleselect "Time Produced"
    field.toggleselect "Width In"
    field.toggleselect "Width Out"
    set field = activedocument.fields("Expressions")
    field.select "Delay Time"
    field.toggleselect "Run Time"
    field.toggleselect "Speed"
    field.toggleselect "Tons In"
    field.toggleselect "Tons Out"
    field.toggleselect "Yield"
end sub

So instead of selection, select in field, it was external, run macro. The problem I hit was that the chart ignored the selections when doing it the non-macro way until I'd made one selection manually, then it realized it needed to do something. Anyway, doing the same exact thing with the macro worked, so I didn't try to figure out the problem with the action. I'm on an oldish version of QlikView. Might be a known problem that's been fixed. Might be something stupid I did.

As an aside, I also made a button, so after the user hits clear, they can easily get back to the default chart.