topic Re: Need help in plotting bar chart in QlikView

Need help in plotting bar chart

Tue, 02 Oct 2012 01:00:57 GMT

hi guys, I have a issue on qlikview. I'm still new about this software so go easy on me.

Here's my problem, I have a bar chart with severeal time bucket as dimension. The code I use is as followed

=Time(MakeTime(left(TimeStart,2),Right(TimeStart,2)),'hh:mm') -----> where TimeStart is 03:00, 03:10, 03:20 . . . . 07:10

and my expression is as follow

Count (

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

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

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

if([DISPT_DT] >= Timestamp#('03:30','hh:mm') and [DISPT_DT]<= Timestamp#('03:40','hh:mm'),'03:30',

if([DISPT_DT] >= Timestamp#('03:40','hh:mm') and [DISPT_DT]<= Timestamp#('03:50','hh:mm'),'03:40',

if([DISPT_DT] >= Timestamp#('03:50','hh:mm') and [DISPT_DT]<= Timestamp#('04:00','hh:mm'),'03:50',

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

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

if([DISPT_DT] >= Timestamp#('04:20','hh:mm') and [DISPT_DT]<= Timestamp#('04:30','hh:mm'),'04:20',

if([DISPT_DT] >= Timestamp#('04:30','hh:mm') and [DISPT_DT]<= Timestamp#('04:40','hh:mm'),'04:30',

if([DISPT_DT] >= Timestamp#('04:40','hh:mm') and [DISPT_DT]<= Timestamp#('04:50','hh:mm'),'04:40',

if([DISPT_DT] >= Timestamp#('04:50','hh:mm') and [DISPT_DT]<= Timestamp#('05:00','hh:mm'),'04:50',

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

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

if([DISPT_DT] >= Timestamp#('05:20','hh:mm') and [DISPT_DT]<= Timestamp#('05:30','hh:mm'),'05:20',

if([DISPT_DT] >= Timestamp#('05:30','hh:mm') and [DISPT_DT]<= Timestamp#('05:40','hh:mm'),'05:30',

if([DISPT_DT] >= Timestamp#('05:40','hh:mm') and [DISPT_DT]<= Timestamp#('05:50','hh:mm'),'05:40',

if([DISPT_DT] >= Timestamp#('05:50','hh:mm') and [DISPT_DT]<= Timestamp#('06:00','hh:mm'),'05:50',

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

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

if([DISPT_DT] >= Timestamp#('06:20','hh:mm') and [DISPT_DT]<= Timestamp#('06:30','hh:mm'),'06:20',

if([DISPT_DT] >= Timestamp#('06:30','hh:mm') and [DISPT_DT]<= Timestamp#('06:40','hh:mm'),'06:30',

if([DISPT_DT] >= Timestamp#('06:40','hh:mm') and [DISPT_DT]<= Timestamp#('06:50','hh:mm'),'06:40',

if([DISPT_DT] >= Timestamp#('06:50','hh:mm') and [DISPT_DT]<= Timestamp#('07:00','hh:mm'),'06:50',

)))))))))))))))))))))))))

------> where DISPT_DT is the time I want to process. E.g. 03:42am will fall into the '03:40' bucket and count increase by 1.

But the problem is I am getting this graph instead. The image attached.

Any help will be appreciated.

Re: Need help in plotting bar chart

whiteline — Tue, 02 Oct 2012 12:21:10 GMT

Hi.

It seems, your expression isn't fully correct as you haven't used aggr().

It could be match easier to use class() function for this, as you have equal size baskets.

(class() as dimension and simple count() as expression)

Re: Need help in plotting bar chart

Tue, 02 Oct 2012 14:52:52 GMT

Hi whiteline,

actually I tried doing class, aggr, count, if-else loop, valuelist, etc

all doesn't seems to work, definitely I'm missing out something but I can't spot it.

thanks for the reply. Could you demo a small example for me?

1) What to put in dimension?

2) What to put in expression?

3) any things to load script?

these three should be the key part i think

Any printscreen will be super helpful to me.

actually there is this other post that have my qvw and excel file

http://community.qlik.com/message/265170#265170

You can download it to see if it works.

Re: Need help in plotting bar chart

whiteline — Tue, 02 Oct 2012 15:27:50 GMT

Hi.

Seems you oversimplified your problem here, as:

First, you have more than one 'time' field to classify.

Second, you want to see empty baskets.

If I suggest you use =time(subfield(class(DISPT_DT, 1/24/6), ' ', 1), 'hh:mm') as dimension and just =Count (DISPT_DT) it will not sutisfy you.

As Jonathan and gwassenaar mentioned, you should use intervalmatch first.

You've done a half of step - created Time field. Look at help (intervalmatch), its a Start field, now create corresponding End field and use intervalmatch to build a connection between your tables.