topic Re: Multiple fields in one bar chart in QlikView

Multiple fields in one bar chart

Mon, 01 Oct 2012 04:08:55 GMT

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.

Re: Multiple fields in one bar chart

Mon, 01 Oct 2012 06:39:56 GMT

This is my desired result.

Re: Multiple fields in one bar chart

jonathandienst — Mon, 01 Oct 2012 06:41:07 GMT

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

Re: Multiple fields in one bar chart

Mon, 01 Oct 2012 07:43:06 GMT

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

Here is my qvw and source file

Re: Multiple fields in one bar chart

Mon, 01 Oct 2012 10:06:14 GMT

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

Re: Multiple fields in one bar chart

jonathandienst — Tue, 02 Oct 2012 13:19:20 GMT

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

Re: Multiple fields in one bar chart

Tue, 02 Oct 2012 14:46:41 GMT

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

Re: Multiple fields in one bar chart

kruppas78 — Tue, 02 Oct 2012 14:55:15 GMT

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;

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

Re: Multiple fields in one bar chart

Tue, 02 Oct 2012 15:14:05 GMT

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

but I always got this kind of pattern

Re: Multiple fields in one bar chart

kruppas78 — Tue, 02 Oct 2012 15:19:19 GMT

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

Re: Multiple fields in one bar chart

Tue, 02 Oct 2012 15:21:36 GMT

Hi Stefan,

I couldnt open the file as I'm using personal edition. Do you mind post screenshot or cut/paste the solution here.

Very much appreciated.

Re: Multiple fields in one bar chart

Tue, 02 Oct 2012 15:30:54 GMT

Hi Stefan,

Don't need to post the solution. I manage to figure out myself the rest of it.

Thanks alot for your help. Really save my life.

I have one last problem. Open the image and notice 06:00 suddenly jump to 06:20. Anyway I could also display 06:10 even if it is empty?

Re: Multiple fields in one bar chart

kruppas78 — Tue, 02 Oct 2012 15:42:00 GMT

You can show everyempty timeperiod.

Go to Chart Properties -> presentation -> and disable "supress Zero-Values"

If You dont want to see the Values above 06:00 you can use a WHERE clause.

Re: Multiple fields in one bar chart

Tue, 02 Oct 2012 15:48:47 GMT

It doesn't work, I also tried unchecked 'Suppress Missing' and 'Zero on bars' in Presentation, also unchecked 'Suppressed when value is null' in dimension. And I also checked 'Show all values' in dimension