Get Min and Max dates but considering changes

[#1]

Dear Community,

I have a table that represents the historical changes of an employee situation.

It contains multiple columns that define it (branch, title, grade, etc...) along with a start and end dates.

Any change in the situation (in 1 or more of the fields) will result in a new record with a new start date, the old record will then be filled with an end date.

What I'm trying to get is a historical view for the employee movement, but my dimensions are not initially known (thus I can't do this by script).

It's up to the user to choose which dimensions he wants to display in the report (1 or more from the available fields), and the start/end dates will vary accordingly. I'm basically using the min/max of dates, aggregated by the chosen dimensions.

The challenge is if an employee goes back to a previous situation (previous value of any column), for example he's in Branch1 for 2 years (2009,2010), moves to Branch2 for another 2 years (2011, 2012) and goes back to Branch1 (2013).

In this case, I'll have for Branch1, 2009/2013 as min/max dates, when the employee actually left this branch for 2 years.

I've attached a sample of what I'm trying to do.

Appreciate your help and suggestions

[swuehl · Accepted solution]

That's an interesting problem, but I think it can not be easily solved.

If you want to show your data as requested in some kind of chart, you would need to use a hidden dimension to group your records with e.g. same ID and Branch, but different periods.

I am not sure you can calculate this easily in the chart itself ( but may be wrong here).

You can precalculate this 'blocks' in the script, but you need to do this for every combination of dimensions you may want to use later on (e.g. the three combinations of Title, Branch, Title & Branch in your example).

Then you can create charts with your additional Block dimension (specific to your combination), and hide this dimension in presentation tab.

See attached sample.

[#3]

Hi swuehl ,

Sorry for my late reply.

Actually I'm using this exactly as the syntax in the text box but in a dynamic update trigger to populate another table and then conduct calculations using these min/max dates aggregated correctly.

Based on your solution, I'm now aggregating using the "block" fields depending on the user choice of columns.

Fortunately we were able to limit the possible combinations that the user can choose from because the table currently contains 7 fields (which leads to 120+ combinations) and could be increased.

Many thanks for your time and answer