Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
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, ', ')

1 Reply
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, ', ')

Community Browser