8 Replies Latest reply: Nov 13, 2011 11:20 PM by rohit gupta RSS

    A distinct and chart grouping issue

      Hi,

       

      I have generated a set of data as follows.  There is other data in the set too, but not relevant for this issue.

       

      ID    Date        Week Commencing Date     All records here for example have set and Validsetdate correct for the expression below.

      2     1/9/11      29/8/11

      3     30/9/11    26/9/11

      4     21/9/11    19/9/11

      5     19/10/11  17/10/11

      6     24/10/11   24/10/11

      5     8/11/11     7/11/11

      7     10/11/11   7/11/11

       

      What I want to do is show the line chart grouping (dimension) by week commencing date.  But also I want to distinct the ID field, so ID person 5 only appears once in the chart.  I'm trying to do this by this expression:

       

      =Count(DISTINCT if(Set = 'ForThisChart' and ValidSetDate=1),ID)

       

      What this is doing is a distinct for each of the week commencing groups in the chart.  Therefore ID 5 is still appearing twice in the report, once in W/C 17/10 and once in the W/C 7/11.

       

      Is there anyway to handle this to remove that second 5 person here?  I assumed the distinct would do it, but it's not.  It would be a real headache to remove the second ID from the underlying dataset!

       

      Thanks!
      Nick