Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Equivalent of SQL "HAVING" in chart expression

Hello,

I have the following data:

Call:

LOAD * INLINE [

ID, Date, Person

1, 01/01/2012, Bob

2, 02/01/2012, Bob

3, 03/01/2012, Bob

4, 04/01/2012, Bob

5, 01/01/2012, John

6, 02/01/2012, John

7, 03/01/2012, Michael

8, 04/01/2012, Michael

9, 05/01/2012, Michael

10, 06/01/2012, Michael

11, 07/01/2012, Michael

12, 01/01/2012, Peter

13, 06/01/2012, Peter

14, 07/01/2012, Karl

15, 01/01/2012, Bob

];

I would like to know how many Person are called, by number of calls.

With a chart, I can use a calculated dimension

=aggr(count(distinct ID), Person)

and the expression

count(distinct Person)

or

concat(distinct Person, ', ')

-->

2012-10-18_113328.png

Now, I would like to make a text area that gives me the number of person who are called a specific number of time.

Exemple:

"1 pers. (Karl) called only 1 time".

and

"2 pers. (Bob, Michael) called more than 3 times"

(bold is the result of an expression)

I want this text area to be updated when I change my selection of "Date".

2012-10-18_113833.png

Will result:

"2 pers. (Michael, Peter) called only 1 time".

and

"1 pers. (Bob) called more than 3 times"

In SQL, I guess I can do it with a "HAVING" key word.

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Equivalent of SQL "HAVING" in chart expression

LOL!

Writing the post made me find the solution!

count({$ <Person={"=count(distinct ID) = 1"}>} distinct Person) & '

' & concat({$ <Person={"=count(distinct ID) = 1"}>} distinct Person, ', ')

count({$ <Person={"=count(distinct ID) > 3"}>} distinct Person) & '

' & concat({$ <Person={"=count(distinct ID) > 3"}>} distinct Person, ', ')

View solution in original post

1 Reply
Highlighted
Not applicable

Re: Equivalent of SQL "HAVING" in chart expression

LOL!

Writing the post made me find the solution!

count({$ <Person={"=count(distinct ID) = 1"}>} distinct Person) & '

' & concat({$ <Person={"=count(distinct ID) = 1"}>} distinct Person, ', ')

count({$ <Person={"=count(distinct ID) > 3"}>} distinct Person) & '

' & concat({$ <Person={"=count(distinct ID) > 3"}>} distinct Person, ', ')

View solution in original post