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: 
Not applicable

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

1 Solution

Accepted Solutions
RedSky001
Partner - Creator III
Partner - Creator III

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)

View solution in original post

9 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

Try with this

StopID and ClientName as Dimension

Expression:

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

Hope it helps

Not applicable
Author

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.

RedSky001
Partner - Creator III
Partner - Creator III

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.)

Not applicable
Author

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.

RedSky001
Partner - Creator III
Partner - Creator III

See the attachment

Not applicable
Author

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 ?

RedSky001
Partner - Creator III
Partner - Creator III

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

Not applicable
Author

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'.

RedSky001
Partner - Creator III
Partner - Creator III

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)