Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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.
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.