3 Replies Latest reply: Oct 2, 2012 11:27 AM by whiteline _ RSS

    Need help in plotting bar chart

      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 _

          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

              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 _

                  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.