topic Re: Using Checkboxes to show/hide expression in chart in QlikView

Using Checkboxes to show/hide expression in chart

alanmcgrath — Fri, 18 Sep 2015 12:50:20 GMT

Hi everyone,

I am trying to create a chart that allows selection of expressions via a checkbox. I was able to get it to work when having one list box of metrics to select from. However, due to the amount of metrics in the list box I would like to separate into smaller list boxes. The issue is once I have separated into a few list boxes I cannot select metrics from each of the list boxes and have them display, it only displays selections from one list box at a time. Any suggestions on how to allow selecting from each list box an have all those selected display?

Here is how create the separate list boxes:

Metrics:
Load * Inline [_metrics, Flag
Sales %,1
SalesHrs,1
Training %,1
Training Hrs,1
Overtime %,1
Overtime Hrs,1
Recovery %,1
Recovery Hrs,1
Total Hrs,1
Unassigned %,1
Unassigned Hrs,1

Attrition Avg Headcount,2
Attrition Avg HP Headcount,2
Managed Leavers,2
Managed Attrition %,2
Total Attrition %,2
Total Leavers,2
Unmanaged Leavers,2
Unmanaged Attrition %,2

Headcount FTE,3
Headcount Heads,3
Joiners FTE,3
Prior Year End Headcount,3

Then in the listbox I am using an expression to display the metrics by "Flag". =If(Flag=1,_metrics)

For the Chart I have all the metrics listed and then am using substring counts to display. So in the general tab I have the calculation condition of GetSelectedCount(_metrics)>0 .

Then in the expression section I have each one listed and in the conditional section I have the formulas =Substring(Concat(_metrics,'|'),'Overtime Hrs') . This is an example of the overtime hrs conditional statement, obviously each expression has the specific title.

When I click on a metric from the list box with Flag 1 it displays fine, but the issue is when I want to also see a metric from the box with Flag 2 or 3, it is dropping the metric from the Flag 1 box.

Any way I can fix this??

Thanks!

Re: Using Checkboxes to show/hide expression in chart

Fri, 18 Sep 2015 13:22:27 GMT

Can you share an example of qvw reduced with the data that you described.
From what I understand you load size in the list box conditionally, but taking them all from the same table.

In this way the selection of a list conditioning even if you use a list box with Checkbox, the selection of a list conditioning is always a reset condition on the other why can not function.

If you can provide an example of qvw I can try to solve the problem in another way.

Re: Using Checkboxes to show/hide expression in chart

alanmcgrath — Fri, 18 Sep 2015 13:25:55 GMT

Hi Gennaro,

QVW has been added to the post. Thanks!

Re: Using Checkboxes to show/hide expression in chart

Fri, 18 Sep 2015 13:34:58 GMT

Could you also give me the data file.

So I can reload Data.xlsx

Thanks

Re: Using Checkboxes to show/hide expression in chart

Gabriel — Fri, 18 Sep 2015 13:45:51 GMT

Hi,

If I understand you well...........

"When I click on a metric from the list box with Flag 1 it displays fine, but the issue is when I want to also see a metric from the box with Flag 2 or 3, it is dropping the metric from the Flag 1 box."

This I think QlikView is Working As Designed... Because the selection is metrics 2 or 3 does not have association with selection in metrics 1.

Question:- Are the tables with metrics associated? If so you need to make the tables with metrics as Data Island not associated in anyway.

Re: Using Checkboxes to show/hide expression in chart

Gabriel — Fri, 18 Sep 2015 13:59:34 GMT

Hi,

I now see the attached file and you have the tables as Island.

Question: Why are you creating the table separately using Flag = 1,2,3?

Why don't you have just 1 list box?

Re: Using Checkboxes to show/hide expression in chart

alanmcgrath — Fri, 18 Sep 2015 14:00:29 GMT

Data file posted, THanks!

Re: Using Checkboxes to show/hide expression in chart

Fri, 18 Sep 2015 14:01:29 GMT

Hi Alan,

check the attachment

Before you reload the data should restore the path of file whit data in the script.

Regards

Gennaro

Re: Using Checkboxes to show/hide expression in chart

alanmcgrath — Fri, 18 Sep 2015 14:03:52 GMT

This is a sample of what my app looks like so these list boxes do not have all the metrics my actual app has. The real app has probably over 50 metrics. So when I presented it with one list box, leadership didn't like it because it was too much in one space. They asked to break the main list box up into separate list boxes by category so it would be easier to find what they wanted. So now I have separate list boxes for Productivity metrics, Attrition metrics, headcount metrics, etc. However, they want to be able to display any number of the metrics together at one time and with the separate list boxes this functionality doesn't work. Does that make sense?

Re: Using Checkboxes to show/hide expression in chart

marcus_sommer — Fri, 18 Sep 2015 14:07:15 GMT

An alternatively could be to group your expressions with one or several grouping-fields like:

Metrics:
Load * Inline [_metrics, Flag, _metricsGroupExpressionType, _metricsGroupUserTypRelated

Sales %,1, Quotes, UserGroup A
SalesHrs,1, Amounts, UserGroup B

......

and you put those grouping-fields into a listbox (maybe with a dimension-group) which would reduce by pre-selecting the possible expressions.

- Marcus

Re: Using Checkboxes to show/hide expression in chart

alanmcgrath — Fri, 18 Sep 2015 14:07:39 GMT

Awesome, thanks! So a simple IF statement is all I needed. I knew it was something fairly simple I was missing. This works great, Thanks again!

Re: Using Checkboxes to show/hide expression in chart

jonathandienst — Fri, 18 Sep 2015 14:14:37 GMT

Are the metrics in the listboxes all subsets of the same field (_metrics)? If so, you could use an alternate state trick.

Place each list box into a different alternate state (Create the states first in Document Properties | General). This means that you can independantly select items in each list box.

Now to 're-assemble' the selections, modify the expression like this:

SubStringCount(Concat({State1} _metrics,'|')

& '|' & Concat({State2} _metrics,'|')

& '|' & Concat({State3} _metrics,'|'), ,'Sales %')

SubstringCount is searching through the values for all 3 states. Add more states for more listboxes and update the expression accordingly.