Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
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
Highlighted
Contributor III

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)

View solution in original post

9 Replies
Highlighted

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

Highlighted
Not applicable

Re: Set Analysis or aggregrate problem??

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.

Highlighted
Contributor III

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 -

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

Highlighted
Not applicable

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.

Highlighted
Contributor III

Re: Set Analysis or aggregrate problem??

See the attachment

Highlighted
Not applicable

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 ?

Highlighted
Contributor III

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)

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

Highlighted
Not applicable

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

Highlighted
Contributor III

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)

View solution in original post