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!

[JordyWegman]

Hi Ritaaguiar,

What are the formulas that you are using in the bar chart where there is a problem?

Jordy

Climber

[ritaaguiar]

Hi @JordyWegman

I'm using exacly the same formulas for both files and that is:

Count(Comando)

Comand stands for the commands the user used within the programs.

Old File

New File

[JordyWegman]

When I run a test it doesn't show this fault. How does your datamodel look? It looks like a false relation.

Jordy

Climber

[ritaaguiar]

I guess it's really a  false relation... It's only relating the Program field. It forgot the User field.

 How can I get it to relate correctly?

Qlik Sense doesn't like it when I try to associate

 This is so annoying 🙄

[ritaaguiar]

Ahhhh, FFS He's back with the date error.......

He thinks 02/11/2018 - 2nd November 2018 is 11/02/2018 - 11th February 2018

what's wrong with this 😞

[JordyWegman]

Why do you have those two tables? And not one big table? What is the difference between the two tables?

[ritaaguiar]

@JordyWegman, because I wanted to have a Qlik Sense file which shows all this data in graphics. Having one big file with all this data won't allow Qlik Sense to make associations between the data, and this data is, in fact, all associated, because the second and third data is the processing of the first data.

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

The right file is the data of the first file but processed to obtain the time the user spent using the programs. We have the percentage (if multiplied by 100) of time spent (4th) by the user (1st) on the program (2nd) for each date (3rd).

I will add a 3rd file afterwards, which shows when the user launches and closes the program, so +1 means the user is using a licence to use that program and -1 is when the user closes and no longer is using that licence. this 3rd data is also the result of processing the 1st data.

 This third graphic is to show me which program licences are being used so I can track to see if it's appropriate to either get more program licences of buy less licenses.

Ok so I decided to create a new file (again). This time I loaded the data from all users in the same file. Now instead of having 3 x 4 files I have only 3 files.
It seems to be working better... but there's still some mistakes. The first ever data is still getting an error with the dates, Qlik Sense thinks that 2nd November is 11th February for data1. I noticed that this must be happening because data1 is YYYY/MM/DD and data2 and data3 are DD/MM/YYYY. So I'm going to change data1  to match the others...

[JordyWegman]

I think instead of relating them to each other, you should concatenate the files and add an indicator which you should use in as set analysis in your formula.

For exampe:

BlocoDeNotas:
Load
    *,
    [BlocoDeNotas] as _indSection
From {all bloco de notas]
;

Concatenate (BlocoDeNotas)
Load
    *,
    [LorasTrabalho] as _indSection
from {all bloco de notas with Loras Trabalho}
;

Concatenate (BlocoDeNotas)
Load
    *,
    InOut2 as _indSection
from {all bloco de notas with InOut2} 
;

Make sure that the headers are the same for:

- The names of the users

- The program

- The date

The reason why your date is not working correctly is because you use a timestamp in Bloco de notas. You can get rid of the timestamp by flooring this field. 

Floor(DateProgram)

Makes from:
01-01-2019 15:55:55

This:
43466

Now make this formula:
Date(Floor(DateProgram),'DD-MM-YYYY')

It will give you:
01-01-2019

[ritaaguiar]

@JordyWegman

where do you type that?

Is it in the Data Load Editor?

[JordyWegman]

Yes, that is in the data load editor! Can you show me the Auto generated section and please load all three tables in it. I need to see what the column names are and what the names of the files are.

Do you have a file for each person or is it only the three files you were talking about?

Jordy 

Climber