Calculating Time differences Flexibly
Analytical Need Certain business questions might require us to determine the time that has passed between two status in a flexible manner. Calculating differences between two events often will require using functions such as YDIFF/MDIFF/SDIFF that can be applied to date fields. As these functions make the calculation on the row-level, you will need to have or to create another date field for the same row. Modeling Challenge In our use case, the HR manager wishes to understand the time that passed for a few interviewees between one hiring stage to another. This model has some limitation to it: Different hiring processes could have different hiring stages. For example, one hiring process has a 'Phone interview' and 'Frontal interview' after it while another has 'Phone interview' then 'Home Assignment' and 'Frontal interview' after that. Therefore, we aren't able to determine the number of stages per hiring process. Solution Our ElastiCube table contains detail about each interviewee Id, the hiring process stages, and their dates. On our scenario, as the HR manager might want to analyze any pair of stages and due to the limitation that we cannot know how many stages a hiring process has, self-joining the current table to itself might be a very consuming process. To overcome these obstacles, we will create a field which will calculate the time passed in seconds from a static point in time, until the datetime event of that particular hiring process stage: Here is our newly created field: We can now use this field for our front-end calculation per interviewee while adding the stage in the measured value filter: This gives us the number of seconds it has been between the two stages per interviewee. That's it!