Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Customisable table problem

I have developed a customisable table allowing users to select dimensions and metrics from a list.

I am loading the following Excel table in my script -

   

_metrics_metricsNo%_metricsgroup
Number of enrolments1Activity
Number of credits2Activity
% of total enrolments3Activity
% of total credits4Activity
% completed successful5Success
Number completed successful6Success
% partial success7Success
Number partial success8Success
% further withdrawal9Success
Number further withdrawal10Success
% early withdrawal11Success
Number early withdrawal12Success

I am using the conditional checkbox for the expression and (as an example for Metric number 12) using the following expression -

SubStringCount(Concat(_metricsNo, '|'), '12')

However, whenever a user selects metrics number 10,11,12 the table also shows metric 1 and 2.  How do I change the expression to avoid that?

Thanks

Greg

1 Solution

Accepted Solutions
prieper
Master II
Master II

Format with _metricNo with two digits: NUM(_metricNO, '00') should do

View solution in original post

5 Replies
prieper
Master II
Master II

Format with _metricNo with two digits: NUM(_metricNO, '00') should do

sam_grounds
Contributor III
Contributor III

I also have created customizable pivot table objects using a similar concept.

What I find works well is creating list boxes which are presented a toggle-able (check boxes) and then using this as the expression for the condition for each dimension (or expression, I have choices for each).

GetFieldSelections(_metrics) LIKE '*Number of enrolments*' OR GetFieldSelections(_metrics) LIKE '*ALL*'


Put a version of this with each metric name within the first LIKE criteria.


The drawback with this method is that when the selections default to 'NOT Number of enrolments' because all but that are selected, it breaks it, but I don't find people tend to use that many metrics, so it works well.

Also, I'd set a pivot to always fully expanded. That works best.

Hope this helps,

Sam

patroser
Partner - Creator
Partner - Creator

Hi Greg,

we usually wrap the ID in non-printables (chr(30)).

let vSep=chr(30);


LOAD (...),

$(vSep) & _noMetrics & $(vSep)as _noMetrics

You need to wrap your _metricsNo in those non-printables within script and can access them from UI as following:

SubStringCount(Concat(Dim), vSep & '12' & vSep )

Hope this helps.

Regards,
Patrick

sebastianlettner
Partner - Creator
Partner - Creator

Hallo,

the problem is, that if you select metric 10 you Concat-String looks like '10'.

SubstringCount('10', '1') is true.

Just add a separator around each metricNo (e.g. chr(30))

Now you can use SubstringCount(Concat(MetricNo), chr(30) & '1' & chr(30)) (chr(30) = record separator)

If you select metric 10 your Concat String looks like '[RS]10[RS]' and SubstringCount('[RS]10[RS]', '[RS]1[RS]') is false

Regards

Sebastian

Kushal_Chawda

try with below expressions for each metrics

1) To display metrics with number 10

=WildMatch(Concat( '|' &_metricsNo&'|',','),'*|10|*')

2) To display metrics with number 11

=WildMatch(Concat( '|' &_metricsNo&'|',','),'*|11|*')

3) To display metrics with number 12

=WildMatch(Concat( '|' &_metricsNo&'|',','),'*|12|*')

4) To display metrics with number 1

=WildMatch(Concat( '|' &_metricsNo&'|',','),'*|1|*')


like wise for all the expression number