Dynamic filter for dates using Table Filter and Charts

[Darcy Godden]

Hi there,

I am wanting to calculate probation and pay increase dates in the below table, can you please assist me with the correct code?

Start Date + 6 months = probation end date

Start Date + 12 months = Year 1: Award increment increase

Start Date + 24 months = Year 2: Award increment increase

Start Date + 36 months = Year 3: Award increment increase

Start Date + 48 months = Year 4: Award increment increase

I have attempted to complete this, however, it returns an error. 

Thank you

[Katerina Rudkovskaya [Stiltsoft]]

Hi @Darcy Godden,

As I see, your periods are not constant: the first one is 6 month, others are one year long. So, we can’t take the first date and add the same date range to it and then to the previous ones, for example, date 1 = start date + 1 year, date 2 = date 1 + 1 year, date 3 = date 2 + 1 year.
Thus, I can suggest using your own description: we take the start date and get other dates by adding different time periods to this first date.

For this purpose, we need to add another column to your source table:



Here is the SQL query for the Table Transformer macro:

SELECT T1.'Start Date', T1.'Description',
FORMATDATE(DATEADD(MONTH, T1.'Period', TT1.'Base'))
AS 'Date' FROM T1,
(SELECT 'Start Date'
AS 'Base' FROM T1 WHERE 'Description'="Probation end date") AS TT1
Then go to the Settings tab of the macro and set the date format as “d M yy” for the case.

Here is your result:


Hope this helps your case.

[Katerina Rudkovskaya [Stiltsoft]]

And if your periods are the same for all the incremented values (for example, 1 year for all the multiple values), you may use a more complex query to get rid of all the manual work.

Here is how your source table will look:



SET @cnt = 0;
SET @date = (SELECT T1.'Start Date' FROM T1);
SET @Des = (SELECT T1.'Description' FROM T1);
UPDATE T1
SET T1.'Date'=FORMATDATE(DATEADD(month,6,'Start Date')) WHERE 'Description'="Probation end date";
WHILE @cnt < 8
BEGIN
SET @cnt = @cnt + 1;
SET @Des = "Year " + @cnt +": Award increment increase";
SET @date = FORMATDATE(DATEADD(month,12,@date));
INSERT INTO T1 (T1.'Description', T1.'Date') VALUES (@des, @date);
END;
SELECT * FROM T1