Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I have table with tasks (see below).
I need to create bar chart
where in x axis will be timeline (based on Start Date)
and for each date will be two bars:
1) count of task where StartDate = Date of x axis
2)Count of task where FinishDate =Date of x Axis
Finally I expect to get:
01.04.20: 1 and 0
02.04.20: 1 and 0
03.04.20 2 and 0
04.04.20 0 and 1
05.04.20 0 and 3
Tell me please, how to solve this problem?
TaskName | StartDate | FinishDate |
Task1 | 01.04.20 | 04.04.20 |
Task2 | 02.04.20 | 05.04.20 |
Task3 | 03.04.20 | 05.04.20 |
Task4 | 03.04.20 | 05.04.20 |
Hi,
you need to use the IntervalMatch function to create a table with all dates inside the ranges for each task.
Check this sample code:
Data:
LOAD * INLINE
[
TaskName,StartDate,FinishDate
Task1,01.04.20,04.04.20
Task2,02.04.20,05.04.20
Task3,03.04.20,05.04.20
Task4,03.04.20,05.04.20
](delimiter is ',');
Dates:
LOAD Date(MinDate + iterno() - 1) AS Date
while MinDate + iterno() - 1 <= MaxDate;
Load min(StartDate) AS MinDate, Max(FinishDate) AS MaxDate
Resident Data;
inner Join IntervalMatch (Date)
load StartDate,FinishDate
resident Data;
left join(Dates)
load *,
1 as Counter
resident Data;
drop fields StartDate,FinishDate from Dates;
Then you just need to create a chart with Date field as dimension, and sum(Counter) as expression.
Hello!
Thanks, it is almost what we need, but
we need TWO bars for every date:
FIRST = how many tasks was created on this date
SECOND = How many task was finished on this date.
How to solve this?