I have now found a way to do this on GUI level that should also work on scripting level:
- I can generate the numeric value of the present month
- By using the >> Monthstart(TODAY(), -X) << expression and counting down from this value to 0, I can do it.
=> I now have to write a loop and run through that as many times (seven times currently) to generate one new field for every past month of this year.
<=> I would like to be able to do this faster, without the need for (currently 7, max. 12) RESIDENT LOADs...
I know generally how the interval_match function works. I've used it before - but last time I used it, I defined the intervals myself and I had fixed values to match against those - this time, it is upside-down: I have a large number of intervals - between the entry_date and leaving_date which might be different for every employee - and I would have to decide whether a certain month (or date) this year (which I'd also have to generate dynamically) matches any (or several) of those intervals.
Can you please explain how to do this?
Thanks a lot!
But even though the interval is big the interval match will work.
Create a Inline MasterCal, where start year can by anything which you want and end date will be today.
And use the interval match function to get the flag for the person active on that day or not.
I understand so far and I guess that will work.
In the diagram I have already built using interval_match, I have just one "end" of every interval - the upper limit - as my dimension which is fine for that purpose as I catch only a snapshot anyway.
In the diagram I want to build now, I would have to use both ends:
- If I use the start_date only, I might return people as active at a time when they have actually already left.
- If I use the end_date only, I might return people active in a certain month, but who have joined the company only in the middle of that month.
The second of these options would be pretty accurate already as I don't suppose that more than a handful of people are ever hired within a month. That should be sufficient.
I cannot yet imagine how that will have to look, but I will start by carefully reading that document you linked.
Thanks a lot!
P.S.: Well, that document didn't help me much. I'm still puzzled by the fact that I will have to use the fct "upside-down":
- In the example, the fact table has a specific value and I load some intervals and match that value with the intervals.
<=> In my scenario, I have the intervals (in that I have a start_date and an end_date for each employee) and I have to match these to discrete
intervals (year-month periods, which will have to be dynamically generated, so I guess I'll have to load them from an external table)..
Well, I'll compare this to the diagram I already have and do some thinking and I guess I will come up with something.
I'll be back here once I have done some experimenting and I can tell you exactly how far I can go and where I fail.
Thanks so far!