nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Table Transformer - Get cumulative values within time period based on Start-End columns

Bart Demol

Hi,

I have a table (Resource - Project - Start - End - FTE, with dummy data attached for SS purposes) where i transform some stuff on to display the attached gantt.

As you can see, my resource Bryan is over allocated between May 2nd and May 9th. It becomes quite messy to get these details when you have 50+ resources that all have 5-6 projects.

Is there a way tho stack this data into 1 bar/line where it would say 100% - 0% - 100% - 150% - 100% for the given time periods? Bonus points for styling 100%+ parts.

In SQL i would try a common table expression but when i use the WITH operator i get the following error.

e.statements[0].compile is not a function

I also tried the html macro but i'm getting nowhere for now

Accepted answers

All comments

Katerina Kovriga {Stiltsoft}

Hi @Bart Demol ,

Not sure that your original idea is possible due to stacking not only percentages but very different time periods.

As for now I can suggest using the Gantt Chart provided by the Chart from Table macro with the following settings:

Mon 1-1.png

Mon 1-2.png

It seems to me that my variant visualizes the percentages better.

And you may also reuse the source table with the help of the Table Excerpt/Table Excerpt Include macros, wrap the reused table into the Table Filter macro to filter the start dates/resources and use the Pivot Table macro on top to list all the percentages in a neat way:

Mon 1-3.png

Katerina Kovriga {Stiltsoft}

As for the Stacked Column chart, I can suggest trying the Pivot Table <- Chart from Table macros sequence to get smth like that:

Mon 1-4.png

Mon 1-5.png

Bart Demol

Hi @Katerina Kovriga _Stiltsoft_ ,

I removed the percentages because I didn't like the progress-bar stile of percentages for resource allocation. And created a column myself with the text concatted that I show in the gantt. And indeed the stacking per user and project works, however that is not what i want to achieve as the certain time period is the key factor I want to report. Normally I could do this in pure SQL but it seems that a CTE for some reason doesn't work.

Katerina Kovriga {Stiltsoft}

Maybe you can give us a result table that you want to achieve from the source one and the current SQL that you are stuck with? Not sure how you want it to look like.

The Table Transformer uses AlaSQL library, maybe our developers will be able to help.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

server

confluence-server

gantt

table-transformer