Distinct dates in Qlik Sense Desktop

[#1]

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

[oknotsen]

I suggest flooring the dates in your preceding load.

Load floor(DATE) as DATE

[#3]

Thanks.

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

[oknotsen]

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))

[swuehl · Accepted solution]

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?])

[#6]

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.

[jagan]

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.

[swuehl]

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.

[#9]

Works! Thanks!