Bar chart for count of tasks(Start and Finish date) visualization problem

[sprutskih]

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

[fosuzuki]

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.

[sperr0w]

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?