Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Multiple fields in one bar chart

Hi guys, I'm new to qlikview, so I hope you can help me out.

Here is my objective. I got 4 fields with 200+ records in each fields, timing from 03:00 to 07:00hrs (Eg. PREPLAN_DT, DISPATCH_TIME)

I want to create a bucket of 10mins interval. Eg. 03:00, 03:10, 03:20 . . . 06:50, 07:00

So for each field, any records falls within the range will go to the bucket.

My y-axis will show the count of each field.

So 03:00 timing with have 4columns with different counts based on the fields and so on.

I did alot of research on the net and found that I can create the bucket in dimension with something like

=if([PREPLAN_DT] >= Timestamp#('03:00','hh:mm') and [PREPLAN_DT]<= Timestamp#('03:10','hh:mm'),'03:00',

if([PREPLAN_DT] >= Timestamp#('03:10','hh:mm') and [PREPLAN_DT]<= Timestamp#('03:20','hh:mm'),'03:10',

but when I put all 4 fields in 'expression' like Count(DISPATCH_TIME) and Count(PREPLAN_DT), all four columns display the same count.

Any help is appreciated. Thanks in advanced.

1 Solution

Accepted Solutions
kruppas78
New Contributor III

Re: Multiple fields in one bar chart

Hi, i tried to calculate it in the script.

edit your Load script;

LOAD TIME(FLOOR(DISPT_DT,0.006944444444),'hh:mm') AS Time,

.

.

.

in the graph you only need to COUNT(DisptTime).

I hope it might help you.

edit:

i changed it and used this script (details see attached File):

PT:

LOAD TIME(FLOOR(PreplanTime,0.006944444444),'hh:mm') AS Time,

PreplanTime AS PT

RESIDENT Table1;

DT:

LOAD TIME(FLOOR(DisptTime,0.006944444444),'hh:mm') AS Time,

DisptTime AS DT

RESIDENT Table1;

KS:

LOAD TIME(FLOOR(Key_Start,0.006944444444),'hh:mm') AS Time,

Key_Start AS KS

RESIDENT Table1;

KE:

LOAD TIME(FLOOR(Key_End,0.006944444444),'hh:mm') AS Time,

Key_End AS KE

RESIDENT Table1;

-------------------------------------------

13 Replies
Not applicable

Re: Multiple fields in one bar chart

desred result.png

This is my desired result.

MVP
MVP

Re: Multiple fields in one bar chart

Hi

This type of problem is best solved by using intervalmatch in your load script. Check the manual for intervalmatch and search here as there are many posts on this topic.

If you need more assistance, then I suggest that you upload your qvw file (and source files if possible) and someone will be able to provide more detailed help.

Regards

Jonathan

Not applicable

Re: Multiple fields in one bar chart

I do alot of trail and error but to no avail. Seriously require some help.

Here is my qvw and source file

Not applicable

Re: Multiple fields in one bar chart

Any idea anyone? It's important to make this work.

MVP
MVP

Re: Multiple fields in one bar chart

The data file you uploaded does not match the load statement in the qvw file

Not applicable

Re: Multiple fields in one bar chart

Hi, Sorry about that. Now it should work. Place the excel in the same folder as the .qvw

Use the one attached in this reply.

Thanks alot for your help

kruppas78
New Contributor III

Re: Multiple fields in one bar chart

Hi, i tried to calculate it in the script.

edit your Load script;

LOAD TIME(FLOOR(DISPT_DT,0.006944444444),'hh:mm') AS Time,

.

.

.

in the graph you only need to COUNT(DisptTime).

I hope it might help you.

edit:

i changed it and used this script (details see attached File):

PT:

LOAD TIME(FLOOR(PreplanTime,0.006944444444),'hh:mm') AS Time,

PreplanTime AS PT

RESIDENT Table1;

DT:

LOAD TIME(FLOOR(DisptTime,0.006944444444),'hh:mm') AS Time,

DisptTime AS DT

RESIDENT Table1;

KS:

LOAD TIME(FLOOR(Key_Start,0.006944444444),'hh:mm') AS Time,

Key_Start AS KS

RESIDENT Table1;

KE:

LOAD TIME(FLOOR(Key_End,0.006944444444),'hh:mm') AS Time,

Key_End AS KE

RESIDENT Table1;

-------------------------------------------

Not applicable

Re: Multiple fields in one bar chart

Hi Stefan,

I followed your instruction and it does work but my ultimate objective is to include DISPT_DT, KEY_START_DT, KEY_END_DT, all three columns.

The desire result should be like this image desred+result.png

but I always got this kind of pattern

NotIwant.png

kruppas78
New Contributor III

Re: Multiple fields in one bar chart

hi, i edit my post and attached a file with a better output.

Community Browser