1 Reply Latest reply: Oct 18, 2012 5:52 AM by Nicolas MARTIN RSS

    Equivalent of SQL "HAVING" in chart expression

    Nicolas MARTIN

      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.