Using RelativeValue() for a Dimension

[jsm0052]

Hi everyone,

I have the following issue, where I need to show the previous Organization an Employee had worked on based on a Dateto field. This sounds quite straightforward but has been tough to crack!

I attached a PNG screenshot of what I need to accomplish since some folks here cannot download Excel files.

When the Dateto field is 12/31/9999 it is considered that the employee is currently working in an Organization. Any other date in the past is considered as a previous Organization the employee was assigned to.

I first tried using the Previous function but it didn’t work; the function only returns the last Organization in numerical order, so if the last Organization of an Employee is not in a sequence, the returned value will be incorrect. I need to factor in Dateto as well, in this variable.

I’ve tried to use RelativeValue after reading some tips around here, but I’ve been out of luck trying to get it running. Since the field I’m trying to find the last value for is a Dimension, I’m finding a little difficult to set the function working. Most of the times it returns blanks for every record, even if I slice in a seemingly correct manner. If anyone can provide any idea or suggestion it will be greatly appreciated.

Thanks!

[nscheaffer]

First off you need to make sure your Dateto object is a Date datatype. If it isn’t, WebI will sort it as text rather than as a date. In the screenshot you provided there is no way that Dateto can have a Date datatype given values like “31/1/2015” and “12/31/9999”.

I created a free-hand SQL query against a SQL Server to represent your sample data.

SELECT 1 AS [Employee], CONVERT(DATE, ‘05/03/2021’) AS [Dateto], 1001 AS [Organization]
UNION
SELECT 1, ‘12/31/9999’, 1021
UNION
SELECT 2, ‘06/24/2010’, 1056
UNION
SELECT 2, ‘01/31/2015’, 1032
UNION
SELECT 2, ‘12/31/9999’, 1091
UNION
SELECT 3, ‘09/15/2018’, 1042
UNION
SELECT 3, ‘12/31/9999’, 1045

I was able to achieve your stated objective by creating a variable with the Previous function…

=Previous([Organization]; ([Employee]))

Since you didn’t provide your formula using the Previous function it is hard to say what was wrong.

Hope this helps.