topic Problems with Top function in QlikView

Problems with Top function

Tue, 11 Jan 2011 15:28:50 GMT

Hi all,

i'm trying to solve this problem : I have a table with many companies codes, each of them can have different customer codes.

Each contact code has got a priority.

I need to visualize for each company a number of customers set by an external variable (defined by a slider) based on the priority.

So if I set the variable of the customer with value 2, in the table below I need to view only customers B e C for company 1, customer X e Y for company 2, because they have priority higher than others.

Setting variable to 3, I'll view customers B, C and A for company 1, X, Y e Z for company 2. So on..

I was thinking to do this with a TOP function using the variable as OFFSET of the function.

I'm not able to use TOP function grouping by company

Can you help me?

COMPANY CODE	CUSTOMER CODE	PRIORITY
1	A	2
1	B	1
1	C	1
1	D	3
2	X	1
2	Y	2
2	Z	2

Re. :Problems with Top function

martin59 — Tue, 11 Jan 2011 15:47:38 GMT

If you want to use it in a list box, you have to use this expression :

=if(PRIORITY<=vPriority,[CUSTOMER CODE])

But why don't you select your values of PRIORITY ?

Problems with Top function

Tue, 11 Jan 2011 15:49:46 GMT

I think that you should use set analysis.

Create a variable that to set every time vPriority. in the sheet where there is the table open an inputbox and insert vPriority.

So from there you can set it as you want

in the expression that you use in the table write this

(${<Priority={vPriority}>}yourExpression)

Problems with Top function

Tue, 11 Jan 2011 15:58:54 GMT

My problem is what I have to write in "yourExpression".

I had already defined a variable and a slider object setting it.

I'm not able to calculate the : "for each company show me only how many customer are set by the variable basing on their prirority".

I don't know if my explanation is very clear..

Problems with Top function

Tue, 11 Jan 2011 16:11:23 GMT

sorry the correct expression format is this

(${<Priority={'$(vPriority)'}>}yourExpression)

Problems with Top function

Tue, 11 Jan 2011 16:26:31 GMT

in table dinension set Company and the expression is

count(${<Priority={'$(vPriority)'}>}Customer)

Re. :Re: Problems with Top function

martin59 — Tue, 11 Jan 2011 16:33:55 GMT

if you want to do that, the correct expression is this :

count({$<Priority={"<=$(=vPriority)"}>} Distinct Customer)

Re. :Re: Problems with Top function

Tue, 11 Jan 2011 16:57:31 GMT

It seems to be ok.

Your solution is completely different from what I had in mind..

Now I'm trying to show the row only if the expression you posted is different from zero.

Re. :Re: Problems with Top function

Wed, 12 Jan 2011 11:04:40 GMT

The expression suggested by Martin Favier doesn't work completely correct.

Look at code company 1260392 in those example: in the first one (before.jpg) the variable of the priority is set to 5 and both the customers 1182050 and 1260392 are showed.

In the second image (after.jpg) the variable is set to 2 and the table doesn't show customer 1260392. Even if it has priority 3 I need it's showed.

The logic is that if I have only 2 customers, one with priority 2 and one with priority 4 and I set the variable to 2 I need to see also the customer with priority 4.

On the contrary, if have 3 customers, two with priority 2 and one with priority 4 and I set the variable to 2 I need to see only those customers with priority 2 because they have both greater priority than the customer with prior 4.

Any tips?

Thanks a lot

Re. :Re: Problems with Top function

Wed, 12 Jan 2011 15:24:22 GMT

Any suggestions?