8 Replies Latest reply: Dec 7, 2015 1:38 PM by chan go RSS

    Distinct dates in Qlik Sense Desktop

      Hi all.

      I am a newbie, so, please, be patient

      I am testing Qlik Sense Desktop.My datasource is a csv file with columns like this:

       

       

      Timestamp                         User           Executed?          Closed?
      2015-11-11 11:03:27               User1          Yes                Yes
      2015-11-11 11:32:00               User1          No                 No
      2015-11-12 06:33:21               User2          Yes                No
      2015-11-12 08:04:47               User1          Yes                Yes
      
      

       

      I want to create charts with statistics about executed task over time by user.

      My first trouble was with the "Executed?" and "Closed?" fields because to count, i'll need a number, not a string. I solved with the formula:

       

      count({<[Executed?] = {'Yes'}>} 1)
      
      

       

      And well, same with Closed? field.

       

      Now, my problem is related with the Timestamp, because i want a chart about User vs Executed and Closed by Timestamp.

       

      My first task was "normalize" the Timestamp field and extract only the date, using:

       

      Date(Timestamp(Timestamp))
      
      

       

      Now, i have the list of each record in the format:

       

      11/11/2015
      11/11/2015
      12/11/2015
      12/11/2015
      
      

       

      But i can't to distinct with unique dates and show all records related with this date. I tried with Filtered Panels and i don't know how "group the dates".

       

      I searched in this forum and some people refer to use scripts like:

       

      LOAD
         DISTINCT DATE
      FROM DATASOURCE
      
      

       

      But i guess that this piece of code doesn't work in Qlik Sense because i haven't a script menu.

       

      How i can proceed? Thanks!

       

      --chan

        • Re: Distinct dates in Qlik Sense Desktop
          Onno van Knotsenburg

          I suggest flooring the dates in your preceding load.

           

          Load floor(DATE) as DATE

            • Re: Distinct dates in Qlik Sense Desktop

              Thanks.

              Where I can do this? Inside a "expression" in the fx doesn't work. Sorry again. 

                • Re: Distinct dates in Qlik Sense Desktop
                  Onno van Knotsenburg

                  You do this in your load script.

                  If you can show my a code snippet of where you are loading the XLS file in your load script, I can edit that and show you what I mean .

                   

                  Btw, I would also change your yes/no flags in the script into DUALs (showing yes/no but actually acting like 1/0).

                  Alternatively, for those flags (your current count thingie), do something like:

                  sum(if([Excuted?] = 'Yes', 1, 0))

                    • Re: Distinct dates in Qlik Sense Desktop

                      Thanks for the suggestion, very clever.

                      I think that Qlik Sense doesn´t show the load script, the most closer menu is the "Data load editor" that shows something like that:

                       

                      SET ThousandSep='.';
                      SET DecimalSep=',';
                      SET MoneyThousandSep='.';
                      SET MoneyDecimalSep=',';
                      SET MoneyFormat='$ #.##0,00;($ #.##0,00)';
                      SET TimeFormat='hh:mm:ss TT';
                      SET DateFormat='DD/MM/YYYY';
                      SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';
                      SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
                      SET DayNames='lun;mar;mié;jue;vie;sáb;dom';
                      SET LongMonthNames='enero;febrero;marzo;abril;mayo;junio;julio;agosto;septiembre;octubre;noviembre;diciembre';
                      SET LongDayNames='lunes;martes;miércoles;jueves;viernes;sábado;domingo';
                      SET FirstWeekDay=6;
                      SET BrokenWeeks=1;
                      SET ReferenceDay=0;
                      SET FirstMonthOfYear=1;
                      SET CollationLocale='es-es';
                      

                       

                      Thanks again.

                        • Re: Distinct dates in Qlik Sense Desktop
                          Stefan Wühl

                          This is the script section you are looking at, the right place to put in your script code. There are probably several tabs (selectable on the left pane) and if you have used the wizard, one of it may be called something like 'automatically generated section'.

                           

                          You should be able to find a LOAD statement with your four fields loaded from your csv somewhere here.

                  • Re: Distinct dates in Qlik Sense Desktop
                    Stefan Wühl

                    In short, using something like

                     

                    Date(Timestamp(Timestamp)) 

                     

                    just formats a timestamp with date and time part as date, only showing the date part, but keeping the full information stored. That's why a list box (which is looking at the full information) or dimension still shows multiple lines, the values are still distinct.

                     

                    You need to strip off the time part of the timestamps to create date only values, you can do this e.g. using floor() function.

                     

                    See also

                    Why don’t my dates work?

                    Get the Dates Right

                     

                    Besides that, you should be able to use the script editor also in the QS desktop, just open the data editor on the upper left icon drop down menu after you've opened your app.

                     

                    And you can of course also count non-numeric fields, like your Executed? field:

                    count( {<[Executed?] = {'Yes'}>} [Executed?])

                    • Re: Distinct dates in Qlik Sense Desktop
                      jagan mohan rao appala

                      Hi Chan,

                       

                      You can try like this

                       

                      1. count({<[Executed?] = {'Yes'}>} Executed) 

                           2. For date filed try this in script

                       

                      LOAD

                      *,

                      Date(Floor(Timestamp(Timestamp))) AS Date

                      FROM Datasource;


                      Floor() removes the time portion of the datetime field, so you won't get duplicates in listbox.


                      Hope this helps you


                      Regards,

                      jagan.