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

[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:

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:

[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:

[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.

[Bart Demol]

okay... that makes sense since it appears that AlaSQL doesn't support partitioning yet which I need in my common table expression to do this.

Below is an example of what I want the result table to look like (Project column is optional but would of course add quite some information to the resourcing)

So basically, time periods that are overlapping should be split up into distinct periods that have a sum of the overlapping FTE values

[Katerina Kovriga _Stiltsoft_]

Thank you, I'm going to consult with our developers now and get back here, if they come with any suitable solution.

[Katerina Kovriga _Stiltsoft_]

@Bart Demol ,

Please can you provide us with your SQL query that you are trying to use and get a mistake? 

And I also forgot to mention that you may use the Table Filter macro in combination with the Chart from Table macro and filter the Gantt chart by the specific resources (not to show all of them at once).

[Bart Demol]

well my first table is trying to recontruct all the timespans. But AlaSQL can't do over/partition as per the doc. 

Maybe the developers see a way out of this via javascript?

with alldates as (select d.*, ROWNUMBER() over (partition by Resource order by thedate) as seqnum
from ((select T1.'Resource', T1.'Start' as thedate from T1)
union all
(select T1.'Resource', T1.'End' as thedate from T1)
) d
),
datepair as (select d1.Resource, d1.thedate as BeginDate, d2.thedate as EndDate
from alldates d1 left outer join
alldates d2
on d1.seqnum = d2.seqnum - 1 and d1.Resource = d2.Resource
) dp
select dp.Resource, dp.BeginDate, dp.EndDate, SUM(T1.FTE)
from datepair dp join
T1
on dp.Resource = T1.'Resource' and
dp.BeginDate >= T1.'Start' and
dp.EndDate <= T1.'End'
group by dp.Resource, dp.BeginDate, dp.EndDate
order by 1, 2, 3