How to represent time on x axis in dates

[ritaaguiar]

Hi everyone.

I have the following data inserted in excel (this is one data out of many others not represented here, with different usernames and programs).

Basically this data represents the amount of time per work day (columns [Time decimal/7,5hours of work] and [Date]) the user (column [Username]) spent using the program (column [program]). The orange line represents the average time spent.

I would like to do this same graphic on Qlik Sense and I have tried to do so:

 I'm not having much luck as Qlik Sense interpreted the Date field completely wrong... You can notice that on 2 November 2018 02/11/2018 the user did not use the program, however, Qlik Sense filled this date with almost 0,5 decimal time when it should be 0... Because of that, the average is also wrong.

How can I get this right? I tried changing the fields many times but it still doesn't work. The problem lies first with the Date being interpreted completely wrong.

 Any help would be appreatiated!

[pradosh_thakur]

A few checks

1:Whats the default date format in the app. You can check that in the variables at the starting of the script?
2: Are the dates properly represented ? I mean they must not be in string format.

[ritaaguiar]

Thank you for your reply @pradosh_thakur

1. I don't know if this is what you mean but here it goes

 2. This data is obtained from an .txt file.  I guess everyting is a string here?

 But yeah, I guess the field Data (Date - English) is not being recognized as a date field because it doesn't have autoCalendar.

[pradosh_thakur]

your dates are in DD/MM/YYYY but app date is in YYYY/MM/DD format that might be one of the issue. Again your script are autogenerated so can't comment on that .

Jsut try this in a text box =max(Your date field) and tell what is the out put?

[ritaaguiar]

It's an invalid dimension

[pradosh_thakur]

Use a Text/IMAGE object and use the expression. then show us the screenshot.

[ritaaguiar]

 24/01/2019 is, in fact, the most recent date with data.

[pradosh_thakur]

your dates are perfect, if you can add some sample data, you can put some dummy data and i can look into it.

[JordyWegman]

Hi Ritaaquiar,

Try the following script. Check that DecimalSep is '.' and ThousandSep is ',' and not the other way around. Because than the Inline wont work.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;€ -#.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='nl-NL';
SET CreateSearchIndexOnReload=1;
SET MonthNames='jan.;feb.;mrt.;apr.;mei;jun.;jul.;aug.;sep.;okt.;nov.;dec.';
SET LongMonthNames='januari;februari;maart;april;mei;juni;juli;augustus;september;oktober;november;december';
SET DayNames='ma;di;wo;do;vr;za;zo';
SET LongDayNames='maandag;dinsdag;woensdag;donderdag;vrijdag;zaterdag;zondag';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

Table:
Load * Inline [
Name,Program,Date,Number
miguel.saraiva, Autodesk AutoCAD 2019, 05/11/2018,0.416259259259259 
miguel.saraiva, Autodesk AutoCAD 2019, 13/11/2018,0.261259259259259 
miguel.saraiva, Autodesk AutoCAD 2019, 19/11/2018,0.249037037037037 
miguel.saraiva, Autodesk AutoCAD 2019, 26/11/2018,0.295814814814815 
miguel.saraiva, Autodesk AutoCAD 2019, 27/11/2018,0.340962962962963 
miguel.saraiva, Autodesk AutoCAD 2019, 28/11/2018,0.026740748797407 
miguel.saraiva, Autodesk AutoCAD 2019, 29/11/2018,0.659592592592593 
miguel.saraiva, Autodesk AutoCAD 2019, 30/11/2018,0.911888888888889 
miguel.saraiva, Autodesk AutoCAD 2019, 03/12/2018,0.912962962962963 
miguel.saraiva, Autodesk AutoCAD 2019, 04/12/2018,0.702851851851852 
miguel.saraiva, Autodesk AutoCAD 2019, 05/12/2018,1.15892592592593 
miguel.saraiva, Autodesk AutoCAD 2019, 07/12/2018,0.138481481481481 
miguel.saraiva, Autodesk AutoCAD 2019, 10/12/2018,1.12918518518519 
miguel.saraiva, Autodesk AutoCAD 2019, 12/12/2018,0.912333333333333 
miguel.saraiva, Autodesk AutoCAD 2019, 13/12/2018,0.347222222222222 
miguel.saraiva, Autodesk AutoCAD 2019, 14/12/2018,1.01514814814815 
miguel.saraiva, Autodesk AutoCAD 2019, 17/12/2018,0.55737037037037 
miguel.saraiva, Autodesk AutoCAD 2019, 18/12/2018,1.07233333333333 
miguel.saraiva, Autodesk AutoCAD 2019, 19/12/2018,1.07914814814815 
miguel.saraiva, Autodesk AutoCAD 2019, 20/12/2018,0.908333333333333 
miguel.saraiva, Autodesk AutoCAD 2019, 21/12/2018,0.849074074074074 
miguel.saraiva, Autodesk AutoCAD 2019, 26/12/2018,0.438148148148148 
miguel.saraiva, Autodesk AutoCAD 2019, 03/01/2019,1.10651851851852 
miguel.saraiva, Autodesk AutoCAD 2019, 10/01/2019,0.00407407407407407 
miguel.saraiva, Autodesk AutoCAD 2019, 17/01/2019,0.9967140740740741
miguel.saraiva, Autodesk AutoCAD 2019, 18/01/2019,0.0386296296296296 
miguel.saraiva, Autodesk AutoCAD 2019, 21/01/2019,0.493925925925926 
]
;

Create a Bar Chart:

     - Dimension: Date(Date#([Date],'DD/MM/YYYY'),'DD-MM-YYYY')

     - Metric: Sum(Number) (set format to number and %)

Check Add-ons -> Reference Lines

    - Expression: = Avg(Number)

This will be the result:Bar Chart Example

The Dimension expression makes sure you get the right format and automatic good sort.

Jordy 

Climber

[ritaaguiar]

Thank you for your help @JordyWegman

I ended up creating a new file, loading all data again and creating all graphics again. Now the dates are working, however, there is another graph which is not working 🙄

Old File

New File

 I think this all has to do with associations. I'm not sure.

The new file has two kinds of .txt data (I will add a 3rd later...):The data on the right side is obtained from the data on the left side, which was processed.

 The only association I did was between the users (Utilizador) from each table/data file.

On the left we have all commands/interactions (4th) that the user 'pedro.ferreira' (1st) had with the programs 'Autodesk(...)' (3rd) and the time when it happened (2nd).

On the right we have the percentage (if multiplied by 100) of time spent (4th) by the user (1st) on the program (2nd) for each date (3rd).

Basically now the number of commands that the user 'pedro.ferreira' did on the programs is now wrong and it says that the number of commands used for the programs 'Autodesk Autocad 2018'  and 'Autodesk Autocad Civil 3D 2018' is the same which is incorrect.