11 Replies Latest reply: Dec 1, 2014 3:36 PM by Josh Campbell RSS

    Qlik Sense: Simple line chart visualization with date on x-axis

      Hi all,

       

      I'm brand-new to Qlik and really want to love it, but I must admit that right out the gate, I'm completely baffled on how to do even the simplest-seeming thing.

       

      I'm trying to create a dashboard for an IVR system that takes several million calls a month, so I'm playing around with an extract of 3 days' worth of data until I get my feet wet. The first visualization I tried to create is a line chart showing number of calls into the system charted over time. My x-axis data is in the form of timestamps:

       

      9/12/2014 12:00:15 AM

      9/12/2014 12:00:17 AM

      9/12/2014 12:00:22 AM

      etc.

       

      When I wrap the variable (StartTime) in the Date#( ) function it converts it to the long integer-looking data

      41894.000015

      41894.000017

      41894.000022

      etc.

       

      My y-axis is a simple count of CallID's.

       

      No matter how I create the line chart, it tries to display every value of the date, either as-is without wrapping it in Date#(), or as those long numbers. I can't figure out how to format the axis to create intervals based on day (which would be 3 values since it's 3 days' worth of data), or hour (72 values over a 3-day period, presumably I'd be able to display them grouped to whatever # of hours makes sense.

       

      Anyhow, I'm sure I must be missing something extremely obvious, and hopefully once it's pointed out, I can figure other things out as well, but I've literally scoured the documentation and forums, watched videos, and tried a million things, without any success.

       

      Thanks in advance for your help!

        • Re: Qlik Sense: Simple line chart visualization with date on x-axis
          Jonathan Dienst

          Hi

           

          Date#() converts a string into a date value (a numeric value). Date() formats a numeric to display as a date.

           

          Change Date#() to Date()

           

          HTH

          Jonathan

          • Re: Qlik Sense: Simple line chart visualization with date on x-axis
            Jonathan Poole

            The two are complementary and work together. So if you are injesting  dates of the format that you have (written in the example below), you can display it in an alternate formate using date()


            =date(date#('9/12/2014 12:00:15 AM','M/D/YYYY hh:mm:ss TT'),'YYYY-MM-DD')

             

            Important point... date() is only for formatting, the detail is actually still preserved in the field and can yield unexpected duplicate entries.  If you actually want to display a DATE (with no time) , suggest using floor() to round down the timestamps to whole dates as follows

             

            =date(floor(date#('9/12/2014 12:00:15 AM','M/D/YYYY hh:mm:ss TT')),'YYYY-MM-DD')

              • Re: Qlik Sense: Simple line chart visualization with date on x-axis

                Thank you for your comments.

                 

                So I'm able to format the date in this fashion, but what I'm unable to do is have the x-axis be customizable. It either displays every single value of timestamps (thousands), or collapses to a single point when I format.

                 

                I'd like to be able to create an x-axis with the days as labels, or hours, etc. using the same underlying data (which is timestamps).

                 

                How might I do this?

                  • Re: Qlik Sense: Simple line chart visualization with date on x-axis
                    Jonathan Poole

                    Were you using the floor() function ?  This should  get you down to whole days. Just replace '9/12/2014 12:00:15 AM' with your timestamp field.

                     

                    =date(floor(date#('9/12/2014 12:00:15 AM','M/D/YYYY hh:mm:ss TT')),'YYYY-MM-DD')


                    Individual hours will come back like this


                    =hour(date#('9/12/2014 12:00:15 AM','M/D/YYYY hh:mm:ss TT'))


                    Day and hour:


                    date(floor(date#('9/12/2014 12:00:15 AM','M/D/YYYY hh:mm:ss TT')),'YYYY-MM-DD')  & ':' & our(date#('9/12/2014 12:00:15 AM','M/D/YYYY hh:mm:ss TT'))


                    If it doesn't work, kindly upload a small sample or spreadsheet.





                      • Re: Qlik Sense: Simple line chart visualization with date on x-axis

                        Thanks again for your super-quick reply!

                         

                        I have a feeling I'm missing an important step, since none of this is working...

                         

                        I've done the following steps:

                        1. Opened Qlik Sense

                        2. Imported my XLSX file

                        3. Created a new sheet

                        4. Dragged "Line chart" onto the sheet,

                        5. Chosen "StartTime" as my Dimension

                        6. Chosen "CallID" as my Measure: Count([CallID])

                         

                        When I don't modify the function for StartTime, I get all the time stamps along the x-axis.

                        When I do either of the date formatting functions to modify StartTime (date(...), date(floor...), hour(...)), I get a single point.

                         

                        My data looks like this:

                         

                        CallIDStartTime
                        227937779September 12, 2014 12:00:00 AM
                        227937781September 12, 2014 12:00:01 AM
                        227937780September 12, 2014 12:00:01 AM
                        227937782September 12, 2014 12:00:02 AM
                        227937783September 12, 2014 12:00:06 AM
                        227937784September 12, 2014 12:00:06 AM
                        227937785September 12, 2014 12:00:07 AM
                        227937786September 12, 2014 12:00:07 AM
                        227937787September 12, 2014 12:00:09 AM
                        227937789September 12, 2014 12:00:12 AM
                        227937791September 12, 2014 12:00:14 AM

                        I'd like to be able to do all of the following (this is Excel):

                         

                        Hourly view:

                         

                         

                        Daily view:

                         

                        Most granular view (basically showing concurrent calls):

                         

                         

                        Thanks again and sorry I'm being so difficult! I really hope to understand the tool so I can move on to more interesting things!

                         

                        James