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!
Unfortunately, I can't remove that IF statement. I need that to drop the other records in the dataset that are nothing to do with this chart (I didn't list those in the example, but there are 1000s of records this IF removes).
That gave me a good idea for the SProc to bring back another date based on similar criteria to what you've said here! That gets the IDs into the same date groups on the chart so the distinct expression then works perfectly!