Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

creating a Timebar in QlikView

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

1 Solution

Accepted Solutions

Re: creating a Timebar in QlikView

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;

QlikCommunity_Thread_123260_Pic1.JPG.jpg

QlikCommunity_Thread_123260_Pic2.JPG.jpg

QlikCommunity_Thread_123260_Pic3.JPG.jpg

QlikCommunity_Thread_123260_Pic4.JPG.jpg

QlikCommunity_Thread_123260_Pic5.JPG.jpg

QlikCommunity_Thread_123260_Pic6.JPG.jpg

hope this helps

Gruß

Marco

View solution in original post

3 Replies
vgutkovsky
Honored Contributor II

Re: creating a Timebar in QlikView

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

Re: creating a Timebar in QlikView

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;

QlikCommunity_Thread_123260_Pic1.JPG.jpg

QlikCommunity_Thread_123260_Pic2.JPG.jpg

QlikCommunity_Thread_123260_Pic3.JPG.jpg

QlikCommunity_Thread_123260_Pic4.JPG.jpg

QlikCommunity_Thread_123260_Pic5.JPG.jpg

QlikCommunity_Thread_123260_Pic6.JPG.jpg

hope this helps

Gruß

Marco

View solution in original post

Not applicable

Re: creating a Timebar in QlikView

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.