Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
hi
if i am getting you correct....
do you want to remove
5 8/11/11 7/11/11
am i right..
thanks
rohit
Hi Rohit,
Yes, that's right. I only want the first instance of any ID that is picked up.
Hi Rohit,
Yes, that's right. I only want the first instance of any ID that is picked up.
hey nick
remove if(Set = 'ForThisChart' and ValidSetDate=1)
because this condition creates problem..may be by using that condition lies on that condition
may it helps you
thanks
rohit
Thanks Rohit,
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).
So I need that IF in place........
Hi Nick.
Not sure I fully understand the issue. Are you trying to only processes IDs with the minimum Commencing Date?
If so, how about adding a flag to the data set in the load scripts to indicate the min Comm. Date for each ID.
eg.
Load
ID, Date, Comm_Date
From Table1
;
left join
Load
ID,
'Y' as Min_Comm_Date_Flag
min(Comm_Date) as Comm_Date
From Table1
Group by ID
;
Then only process records with Min_Comm_Date_Flag = 'Y' in your expression. Note: The above code has not been tested.
John.
Thanks John,
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!
Cheers
Nick
hey....
use intervalmatch(ID,[Week Commencing Date ])
may it helps you
thanks
rohit