topic Re: Set Analysis or aggregrate problem?? in QlikView

Set Analysis or aggregrate problem??

Tue, 04 Dec 2012 14:13:46 GMT

Hi,

I have the following problem.

Background information :

We have a Mailman, everytime he goes for a delivery he gets a unique id (StopID), in 1 delivery(StopID) he can deliver mail to 1 or more clients.

Example :

StopID Client #Packages

1 A 1

1 C 1

2 A 9

2 B 8

3 C 1

4 A 5

4 D 3

4 C 1

5 D 1

5 A 1

5 B 5

What do i want to see ? Whenever i put the client name in my Qlikview report (made a input box with variable vSynDeb) i want to see all the client names the mailman also delivered in the same StopID.

Example : if i input Client C into the inputbox i want to see :

Client : A,C,D

if i input Client A i want to see:

Client : A,B,C,D

How do i do this ?

i made a straight table with the following expression:

= if(sum (

Aggr(

if(count({$<Client>} StopID) > 1, 1,0)

if(count({$< Client = {'$(vSynDeb)'} >} DISTINCT StopID) > 1, 2,0)

,StopID)

)

=3,1,0)

This way i managed to get all StopID numbers where a mailman delivered to the selected (vSynDeb) client and to other clients on the same StopID. And now im stuck. How do i get the other Client names in the table ?? whnever i insert Client dimension to chart the chart only show 1 Client name and not all the client names that the mailman delivered to on that specific StopID

Re: Set Analysis or aggregrate problem??

CELAMBARASAN — Tue, 04 Dec 2012 14:25:09 GMT

Try with this

StopID and ClientName as Dimension

Expression:

If(Count({<StopID=P({<Client={'$(vSynDeb)'}>} StopID)>})>0,1,0)

Hope it helps

Re: Set Analysis or aggregrate problem??

Tue, 04 Dec 2012 14:33:02 GMT

Hi,

The expression is not working for me. no results are shown.

What does StopID=P mean ?

It does show names now, but too much names. i expect +/-16 unique Client names but got much more +10000ish
I also got StopID numbers with other client names ? I selected (example)Client A and now i also see StopID where only Client D is in it.

Re: Set Analysis or aggregrate problem??

RedSky001 — Tue, 04 Dec 2012 14:49:16 GMT

Create a listbox for Client and select A

Now create a chart with Client as the dimension and the following expression:

Count({1<StopID = p({1<Client={$(=Only(Client) )}>}) >} Client)

What does StopID=P mean ?

See the heading "Set Modifiers with Implicit Field Value Definitions"

on page 809 of the Qlikview reference manual.

Return the clients exludng the current selection (ie you select A but this still returns A,B,C & D)

but only those clients who have have StopID the same as the selected client (A).

(see the attached example)

Update -

(Didn't read down this thread before piosting and see Celambarasan Adhimulam had already replied, might be useful so will leave this response.)

Re: Set Analysis or aggregrate problem??

Tue, 04 Dec 2012 15:19:01 GMT

Hi,

The expression you showed is working but with 1 issue. The table does never change whatever you select(other than Clientname), it now shows every clientname where the StopID is the same as the selected.

I also want that it changes when i select my date field. how do i implement this to the expression of yours ? Sorry that i didn't mention the date field in my first post.

UPDATE:

With your expression i get 42 unique Client names ( this is all the names ever in history where the StopID of the selected Client was the same as another Clients StopID) This number does not change when i select December, in December i must only see the Client names where the StopID in december of the selected client is the same as other clients, in this case 16 unique names.

Re: Set Analysis or aggregrate problem??

RedSky001 — Tue, 04 Dec 2012 15:49:51 GMT

See the attachment

Re: Set Analysis or aggregrate problem??

Wed, 05 Dec 2012 09:32:58 GMT

Thanks! The example explains a lot

I have just 2 more questions.

1: Is it possible to use the Client variable (vSynDeb) in the expression of yours? I have made a input box with variable (vSynDeb = Client) and that one is also used for other charts.

2: In the example file you can also select a date but only 1 date, when i select 2 dates the expression doesnt work anymore. The users of the report would really like to select multiple dates, how can i achieve this ?

Re: Set Analysis or aggregrate problem??

RedSky001 — Wed, 05 Dec 2012 12:55:45 GMT

Yes, change the expression to

Count({1<Date={$(=Concat( chr(39) & Date & chr(39),','))} , Client-={$(vSynDeb)} , StopID = p({1<Client={$(vSynDeb)}>}) >} Client)

Shame I'll only get 10 points for answering your multiple quesitons

Re: Set Analysis or aggregrate problem??

Wed, 05 Dec 2012 13:47:55 GMT

i would give more points if it was possible

do you have time for 1 more question? last one i promise

I found out that StopID can come frome multiple sources(tables) example: Source '1A',Source '2A',Source '3A'. ive got a field with this named Source. This should not be selectable for users but i must only show the results of the expression where the StopID is in Source '2A' or in Source '3A'.

Re: Set Analysis or aggregrate problem??

RedSky001 — Wed, 05 Dec 2012 13:57:31 GMT

Should just be a case of adding another set modifier condition, something like this:

Count({1<Date={$(=Concat( chr(39) & Date & chr(39),','))} , Client-={$(vSynDeb)} , StopID = p({1<Client={$(vSynDeb)}>}) , Source = {'2A','3A'} >} Client)