Sum a field to a variable (x axis is time)

[ritaaguiar]

Hello,

I have a Data file (attached to this post) which informs when (3rd and 4th field in the data file) users (1st field) have opened a software (2nd field) and thus are using a network license of that software. +1 stands for licence being in use and -1 stands for licence no longer in use (5th field). This means that between a +1 and a -1, there is a period of time when the user was using the licence of that software.

I want to create a graphic with Qlik Sense which represents the number of licences being used at each moment in time. This graphic should look something like this:

In regard to the data in the attached file, each line represents a moment in time when the software was either launched or closed (and thus the user starts using a licence or freeing a licence for network use).

Because this data is only telling me when the value is changed, and not the value at each moment given in time, I thought about creating a variable, starting at 0, which will either go up or down, when a user opens or closes the software.

How can I do so? Thank you very much for your help.

[pradosh_thakur]

Hi @ritaaguiar 

Sorry i was busy so couldn'r reply back sooner. So here is the logic

In your data 3 file i have changed hour field to closing hour because i thought we don't need it. As you said we need it so we have to change that.

so how a date/ time works in Qlik is its a dual field looks like a string but inside it a number. Have a look

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/date-time-inte...

So there are few date function https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTim...

There are few like date# , timestamp# and timestamp

i will give the example of one you will get others

So timestamp#() can change string into timestamps and timestamp() changes one timestamp format to another.

So if i have a string in the field 31/12/2018 12:23:34 , i will do num(field) or max(field) if it shows a number it is a time stamps if not we have to change it to timestamp using timestamp#()

so here it goes  timestamp#('31/12/2018 12:23:34','DD/MM/YYYY hh:mm:ss')

I hope you understood it .

[pradosh_thakur]

data3:
load Utilizador& Programa&  Data as join_key , Hora as closing_hour ,Licença  , timestamp(timestamp#( Data  &  Hora,'DD/MM/YYYYhh:mm:ss')) as timestamp_field ;
load * inline [
Utilizador,Programa,Data,Hora,Licença
rita.aguiar,Autodesk AutoCAD 2019,04/02/2019,09:00:00,1
rita.aguiar,Autodesk AutoCAD 2019,04/02/2019,10:00:00,-1
rita.aguiar,Autodesk AutoCAD 2019,04/02/2019,10:00:00,1
rita.aguiar,Autodesk AutoCAD 2019,04/02/2019,11:00:00,-1
rita.aguiar,Autodesk Revit 2019,04/02/2019,11:00:00,1
rita.aguiar,Autodesk Revit 2019,04/02/2019,12:00:00,-1
rita.aguiar,Autodesk AutoCAD Civil 3D 2019,04/02/2019,12:00:00,1
rita.aguiar,Autodesk AutoCAD Civil 3D 2019,04/02/2019,13:00:00,-1
];

[pradosh_thakur]

So Data& Hora will give you the field value for each hour. Now you can use that ion your line chart in X-axis. It should work. If it doesn't we can have a look  at it again

Regards

Pradosh

[ritaaguiar]

Thank you @pradosh_thakur 

At the moment, the x axis is a time continuos scale as I wished for. I don't know yet how to create a formula for the measure that will represent the licences in use how I want it to be:Goal graphic

The attempt 1 shows the obtained graphic from the sample data I sent you (with only one user named rita.aguiar).

Attempt 1

The attempt 2 shows the graphic that I obtained using all data I have so far (4 users).

Attempt 2

As you can see, I haven't managed to get the formula for the measure right for any of the attempts.

[pradosh_thakur]

Did you change the script as i suggested ? And used "timestamp_field" in the x axis.

Please check my previous comments.

Thanks

Pradosh

[ritaaguiar]

Yeah, I think I did not miss anything @pradosh_thakur 

So here is the script (I only changed the hour format from hh to HH because that is more useful for me to understand when its a.m. or p.m.). Then I used the field timestamp in the x axis:. I have attached the Qlik Sense files to this post. I think this graphic might be right, although I realized I don't have enough data on the sample file to see any major changes on the licences in use. I will try to do the same on the complete data.

[ritaaguiar]

@pradosh_thakur 

So this is the full data, where I also applied the timestamp transformation to obtain the timestampt_field for the x axis. It's not quite right yet, but I think it's getting closer. I have attached the file to this post.

[pradosh_thakur]

@ritaaguiar  Looking at it. You have uploaded two qvf. Which one to look at?

[ritaaguiar]

@pradosh_thakur 

Open the file attached to this post.

[pradosh_thakur]

@ritaaguiar  I noticed you are not using the script we have written and created the join key so that the data are properly defined without synthetic key .

 you should be  using Sum( Licença) as your expression i guess.