topic Re: Variable in Set Analysis in QlikView

Variable in Set Analysis

julesjohnson — Sat, 15 Jul 2017 00:29:52 GMT

Hi,

Can anyone tell me how to fix this expression.

=Count( {$<$(ROIC)= '{">=1} >} Distinct [Part_Number])

I would like to count the number of parts where the variable ROIC" is greater than 1, for example.

Thanks,

Re: Variable in Set Analysis

jcampbell474 — Sat, 15 Jul 2017 01:51:52 GMT

Is the variable ROIC a single value or a list? Please hlep me understand what you're trying to do.

Re: Variable in Set Analysis

julesjohnson — Sat, 15 Jul 2017 01:54:04 GMT

Hi Jason,

It is a calculated value derived from Profit/ Investment.

Re: Variable in Set Analysis

jcampbell474 — Sat, 15 Jul 2017 01:58:12 GMT

Ok, so it's a static value? Is it always > 0?

Re: Variable in Set Analysis

julesjohnson — Sat, 15 Jul 2017 02:00:13 GMT

Hi Jason,

It is calculated so no, not static. Yes, there is a possibility that it can be negative.

Re: Variable in Set Analysis

jcampbell474 — Sat, 15 Jul 2017 02:08:17 GMT

Thank you. Do you have a field associated with the variable (ROIC)? It looks like you're using set analysis to create a data set using ROIC, but I don't see a field that can be >=1 in your expression. In other words, the variable can be >=1, but the variable isn't linked to a field the correlates to the Part Number. If you have the field, I might be able to help with the expression.

Re: Variable in Set Analysis

julesjohnson — Sat, 15 Jul 2017 02:16:29 GMT

Hi Jason,

I am not sure I get what you are asking.

What I am attempting to do is say count the number of parts where the calculation for ROIC > 1.

In the following example, the number returned should be 2.

Part #	Description	ROIC
z45	Crank	-2
x25	Lever	5
f85	cover	0
h85	line	1.2

Re: Variable in Set Analysis

jcampbell474 — Sat, 15 Jul 2017 02:21:01 GMT

Ok, that makes more sense. ROIC isn't a variable, it's a field.

Try this: count({<ROIC={'>=1'}>} Distinct Part_Number)

Of course, the result will depend on what dimension you use. Using your example, I would use Description.

Re: Variable in Set Analysis

julesjohnson — Sat, 15 Jul 2017 02:32:04 GMT

Thanks for the suggestion but unfortunately that did not work. ROIC is a variable so the $ expansion will need to be added. It is also a field in a table, but I would like to create buckets for a graph where the fields in the table will not be used.

Re: Variable in Set Analysis

jcampbell474 — Sat, 15 Jul 2017 02:37:39 GMT

Sorry about that. Will this work: count({<ROIC={">=$(ROIC)"}>} Distinct Part_Number)

Re: Variable in Set Analysis

julesjohnson — Fri, 21 Jul 2017 15:11:35 GMT

Hi Jason,

Just wanted to let you now that I was never able to make this work. Thanks again for your help. I will keep working on it.

Thanks,

Juliette

Re: Variable in Set Analysis

vishsaggi — Fri, 21 Jul 2017 15:24:46 GMT

Can you share a sample app where you have the issue? Where you want to display this data in a text object?

What value can you see for your ROIC variable when used in a text object?

Re: Variable in Set Analysis

jmvilaplanap — Fri, 21 Jul 2017 15:48:36 GMT

Do you have a quote in your expression

Try to take it off and add another one after the 1

=Count( {$<$(ROIC)= {">=1"} >} Distinct [Part_Number])

Re: Variable in Set Analysis

julesjohnson — Fri, 21 Jul 2017 16:12:58 GMT

I'm still getting an error in the expression.

Re: Variable in Set Analysis

jmvilaplanap — Fri, 21 Jul 2017 16:16:27 GMT

Hi,

It doesn't matter, when you are using variables, the expression checker is not working properly, try if works.

Re: Variable in Set Analysis

vishsaggi — Fri, 21 Jul 2017 16:16:55 GMT

Try this?

= Count({< ROIC = {"$(= $(ROIC) >= 1)"} >} DISTINCT [Part_Number])

Re: Variable in Set Analysis

sunny_talwar — Fri, 21 Jul 2017 18:45:30 GMT

I would think something like this

=Count({< Part_Number = {"=$(ROIC) >= 1"} >} DISTINCT [Part_Number])