You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
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.
1) Input data into the following rows based off these values:
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.
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.
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.