Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,456,601
Community Members
 
Community Events
176
Community Groups

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

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:

Date_And_Color_Table.png

 

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.

2 answers

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.

0 votes
G subramanyam Community Leader Dec 22, 2022

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

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events