Table Transformer: Populate Dates & Cell Color Based on Cell Value

[Cliff Wilson]

Hi All,

I am a very much so a novice at SQL, but have good working knowledge of Excel and am trying to replicate some functionality using Stiltsoft's Table Transformer. I've tried to come up with a solution, but was unsuccessful and could use some advice.

Goal:

1) Input data into the following rows based off these values:

• Release row will have date manually added as the source for the below calculations:
  • BizDev (-20 Wks from Release Date in each respective Column)
  • Hard Requests (-16 Wks from Release Date each in respective Column)
  • Easy Requests (-12 Wks from Release Date each in respective Column)
  • Events (-8 Wks from Release Date in each respective Column)
  • News (-4 Wks from Release Date in each respective Column)

2) Color the cell background in a light red color automatically, for each of the above rows when the date listed in the cell exceeds today's date.

3) Any of the blank rows (Planning, Testing #1 & #2) will have manual dates added and don't need any coloration or special functions.

Example Image:

Current Attempts:

UPDATE T1
SET T1.'12.0' = DATEADD(wk, -20, (SELECT 12.0 FROM T1 WHERE Task = 'Release'))
WHERE Task = 'BizDev';
SELECT * FROM T1

This was a very basic attempt from my limited knowledge to see if I could adjust just the first date as a proof of concept, but was unsuccessful and I was unable to identify why.

If anyone has any advice, could point me in the right direction, or knows of a solution I would greatly appreciate any help.

[Katerina Rudkovskaya _Stiltsoft_]

Hi @Cliff Wilson,

For the first part of the question, we suggest checking this Community thread: the dates there are counted based on the first date in a column and placed in the same column. To count dates, the second table with days between your milestones is considered.

The first variant excludes weekends and prolongates the periods accordingly, the second query is a more advanced one and excludes custom holidays as well.

For the second part of the question, you need to use the second Table Transformer macro. The first query is rather complex, so it is better to separate the queries. Several examples about the conditional formatting can be found in our documentation.

For the third part of the question, where you want to add rows with manual data, I can suggest using a separate table that later you just merge with the first table with calculated dates (one TT to calculate dates, the second TT to merge two tables, the third one to color background). But the difficulty will be with the conditional formatting, this part of the table will be colored as well.

Hope it helps your case. If you are still stuck with the query, you may refer to our support. Attach the page storage format (upper right corner of the page -> menu … -> View Storage Format), so that we’ll be able to recreate your source table and your Table Transformer macros with the queries and investigate the issue. If you don’t see the option, please ask your Confluence administrator to do it for you.

Also, maybe it will be more convenient to use the Table Spreadsheet macro for the case? The macro has all the basic Excel functionality (cell formulas, conditional formatting, etc.). Seems that your case is a bit overloaded for the Table Transformer macro and SQL queries.

[G subramanyam]

Hi @Cliff Wilson welcome to the Atlassian community.

May I request you to reach out to the Stiltsoft's support team as well.

Some helpful links:

Table Transformer macro

Email/ support request link