3 Replies Latest reply: Nov 23, 2015 12:31 PM by Jonathan Poole RSS

    Unable get date format correct

      Hi all,

       

      im new to Qlik, so please be patient with me on here.

       

      I have watched the tutorials, looked through the support and community posts, but i still cannot get my head around how the date format seems to change in Qlik desktop.

      I have attached a couple of files to help explain the issue i have.


      I can load the data from the simple excel sheet (attached) and build the app fine, but once i start to add various dimensions and measures in, the date goes all to pot as in the attached pdf.


      Once the date format has gone bandy, then even if i delete the charts, the dates are still wrong.


      i have read that i need to add some code to fix this, but im confused about where to add this code to fix the issue, which im sure is just me not understanding, rather than a Qlik problem.


      any help would be great.


      Phil

        • Re: Unable get date format correct
          Jonathan Poole

          i think i have some idea of what you are trying to do, but i need to ask:  what is the height of the linedesigned to show ?  (per site or short name)

           

          Is it the maximum 'date from' ?  minimum 'date from' ?  avg ?

           

          max(date_from)

          min(date_from)

          date(avg(date_from))

           

          are all valid

            • Re: Unable get date format correct

              Hi Jonathan,

               

              At the moment i am just seeing what the app can do, as my CEO wants to use it and then move to Enterprise once we have a handle on it, but so far im struggling just to understand the impact of the date format.

               

              I was using sitename, the shortname is the billing code for each site, but i didnt expect that adding both would give strange date ranges.

               

              i did try all of the valid ranges, but sometimes the dates are fine, sometimes way out.

              max(date_from)

              min(date_from)

              date(avg(date_from))

               

              What im trying to get my head around, is why the dates are so far off from the dates in the excel sheet,

               

              In the attached pdf, they were examples of what happened when choosing different measures.

               

              Am i doing something daft here, do i need to set constraints on the date ranges?

               

              phil

                • Re: Unable get date format correct
                  Jonathan Poole

                  Right now you are using a date field as a numeric measure. The height of the line is tied to a numerical representation of date.  That is fine when using min(), max(), avg() ... so long as qlik is interpreting your data source as bona fide dates,  but if you do a sum() it will make no sense.  

                   

                  That is why i needed to know what you expected when you summed 2 dates together. 

                   

                  ie:  jan 1 2015  +  jan 4 2014 =  ??

                   

                  Your actual measure may be something like sum(Sales) and you want to see these sales by shortname/sitename while bucketed out by different dates or date buckets . In that situation you need to add a 2nd dimension to represent the date and of course a new measure.

                   

                  What you are trying to achieve is very important in how to answer your question .