Chart with different dimensions

[talita_verdes]

Good Morning

I have a question and I do not know how to solve it

Following:
I have a telemarketing employee rests table by estimated hour and time taken.
The table is as follows:

EmployeeID	Hour estimated	Hour taken
1	09:00	09:00
2	09:00	09:00
3	15:00	09:00

The problem is that I have to do a line chart per hour indicating how many people were estimated to pause at 9:00 and how many actually stopped.

In the example above was to have the following answer:
At 9:00 am Estimated pause = 2, pause performed = 3

The biggest problem is that the time dimension is different columns.
If done in different graphs is correct when together gives always the same value to realized and estimated.

Already tried:

1- Create a separate dimension of estimated time

2- Count by set analysis disregarding the dimension

3- Have already tried count employeeId with IF

What can I do?

Thanks

[sunny_talwar · Accepted solution]

Check this out

Script

Table:

LOAD * INLINE [

    EmployeeID, Hour estimated, Hour taken

    1, 09:00, 09:00

    2, 09:00, 09:00

    3, 15:00, 09:00

];

LinkTable:

LOAD Distinct EmployeeID,

[Hour estimated] as Hour,

'Estimated' as Flag

Resident Table;

Concatenate (LinkTable)

LOAD Distinct EmployeeID,

[Hour taken] as Hour,

'Taken' as Flag

Resident Table;

Straight table

Dimension

Hour

Expressions

=Count(DISTINCT {<Flag = {'Estimated'}>} EmployeeID)

=Count(DISTINCT {<Flag = {'Taken'}>} EmployeeID)

Pivot Table

Dimensions

Hour

Flag

Expression

=Count(DISTINCT EmployeeID)