creating a Timebar in QlikView

[#1]

Hey all,

i want to create a Timebar in QlikView.

There is a table with states of machines, every machine got at one timepoint only one state.

In this table are StartDate, EndDate, State and MachineID columns.

I need a stacked bar chart with the MachineID as Y-Axis and the Date as X-Axis.

The stacked bar should be represent the timeslices of states of the machines.

I tried it with a GANTT chart in QlikView, but i have a problem with the stacked bars.

Attached you can find a Screenshot which shows you how the Timebar should be appearance, a Excelfile with Sampledata and the .qvw of my test.

I hope somebody got a solution for me...

Thank you!

EDITED:

I added another .qvw,there you can see the correct timeslices, but still not stacked

[vgutkovsky]

Interesting challenge. You'd need to start by creating a calendar of all possible time points and use the intervalmatch function to connect it to your Fact table. Note that this results in a 7M row calendar table if you want to keep the granularity in seconds. I'm assuming that minutes is accurate enough though--this let me shrink the final calendar size to 115K rows. Also, if you're unfamiliar with intervalmatch, note that this is one of the few times in QV that synthetic keys are actually best practice.

For the UI piece, I kind of cheated and created a trellis chart. Let me know if that doesn't work for your purposes.

Regards,

Vlad

[MarcoWedel · Accepted solution]

Hallo Mario,

another solution could be:

LOAD MachineNo, 
     Timestamp(If(1-IterNo() ,EndDate-1/86400, StartDate), 'DD.MM.YYYY hh:mm:ss') as Date,    
     Status
FROM [http://community.qlik.com/servlet/JiveServlet/download/555941-112657/Data_demo.xlsx] (ooxml, embedded labels, table is Tabelle1)
While IterNo()<3;

hope this helps

Gruß

Marco

[#4]

Thank you for this great solutions!

The solutions have both advantages and disadvantages, but in fact that the machine_data table is very large, i decided for the solution of Marco Wedel. I've got heavy performance issues with the first solution on a large amount of data.

The second solution don't looks so great but it works performant and its very easy to implement.