How to make some group changes

[#1]

Hi Everybody.

Few days ago, i asked the way to do some groups and Mr John Witherspoon gave me the answer as follows:

[User Groups]:
LOAD
"User Group"
,subfield("Users",',') as "User"
INLINE [
User Group: Users
Group1: User1, User3
Group2: User2, User4
] (delimiter is ':');

It works so good, but now i have a problem, a user changed group, and now i have to change it, but for statistics i need that the user belongs to the first group until a specific date and after pass to the be part of another group,... How do i do this?

Sorry for my english.

Best Regards.

[johnw · Accepted solution]

You need to add the date to the data structure.  Here's one way:

LET vMinDate = num(makedate(2011,1,1)); // You would establish this from your calendar or data set
LET vMaxDate = num(makedate(2011,2,28)); // also from your calendar or data set

UserGroups:
LOAD
User
,date(if(End,End,$(vMaxDate))) as End
,date(if(User=previous(User),peek(End)+1,$(vMinDate))) as Start
,UserGroup
INLINE [
User, End, UserGroup
User1,,Group1
User2,,Group2
User3,1/15/2011,Group1
User3,2/20/2011,Group2
User3,,Group3
User4,,Group2
];
UserGroups2:
LOAD
User
,date(Start+iterno()-1) as Date
,UserGroup
RESIDENT UserGroups
WHILE Start+iterno()-1 <= End
;
DROP TABLE UserGroups;

[#3]

Thanks for your answer; initially it works!, now i´m going to make some probes with my project. I really appreciate your help.