9 Replies Latest reply: Dec 5, 2012 8:58 AM by Mark Sheraton

# Set Analysis or aggregrate problem??

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??

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??

Hi,

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??

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 -

• ###### Re: Set Analysis or aggregrate problem??

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??

See the attachment

• ###### Re: Set Analysis or aggregrate problem??

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??

Yes, change the expression to

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

• ###### Re: Set Analysis or aggregrate problem??

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??

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)