topic Re: How do i create a Time Based Chart for this? in QlikView

How do i create a Time Based Chart for this?

Thu, 22 Dec 2011 13:00:24 GMT

I have 3 fields for example: ContractID, Start_Date, End_Date

I want to create a bar chart for the last 12 Months that counts all contract IDs in that chart month that have start_date<=chart month and End_date>=chart month - I want to see how many active contracts in any month over a 12 month period.

Re: How do i create a Time Based Chart for this?

Anonymous — Thu, 22 Dec 2011 13:42:58 GMT

Take a look at the attached file for a possible solution.

I hope this is helpfull.

Good Luck,
Dennis.

How do i create a Time Based Chart for this?

Thu, 22 Dec 2011 16:10:51 GMT

We are currrently evaluating QlikView so I only have the Personal Edition installed at present. I am unable to open any files that weren't created using my installed copy.

Re: How do i create a Time Based Chart for this?

Anonymous — Thu, 22 Dec 2011 18:34:47 GMT

I will try to explain what I did forgiozedzed1 here:

http://community.qlik.com/message/168502#168502

I used his attached Excel file for the data.
His question was simulare to yours, only his question was about days.

1 When you load that Excel file you can use the following script:

Data:

LOAD name,

number,

Afdeling,

Salaris,

birthday,

Datumindienst,

IF(ISNULL(Datumuitdienst) , 'Still Active' , Date(Datumuitdienst)) as Datumuitdienst

FROM

[Kopie van personeel.xls]

(biff, embedded labels, table is Blad1$);

2 Then make 2 new variables (CTRL+ALT+v)

vStartDate

vEndDate

3 create an inputbox with the variables

4 Creat below Macro (CTRL+m)

sub SelectDatum

ActiveDocument.ClearAll false

First= ActiveDocument.Variables("vStartDate").GetContent.String

Last= ActiveDocument.Variables("vEndDate").GetContent.String

mySelect= ">=" & First & " <=" & Last

ActiveDocument.Fields("Datumindienst").Select mySelect

ActiveDocument.Fields("Datumuitdienst").Select mySelect

ActiveDocument.GetField("Datumuitdienst").ToggleSelect "Still Active"

end sub

5 Create a button with an action to start the macro

Properties >> Action >> Add : External >> Run Macro >>OK

Marco Name: SelectDatum

Fill in the inputbox the startdate (vStartDate) and the enddate (vEndDate) and press the button to execute the selection.

The macro selects the range on the fiels Datumindienst and Datumuitdienst

The last line in the script is optional, this line ad the `Still Active` ones to your selection on field Datumuitdienst.

("Still Active" is created in the script when there is no value in the field Datumuitdienst.

I hope this makes sence to you.

Dennis.

Re: How do i create a Time Based Chart for this?

Anonymous — Thu, 22 Dec 2011 18:37:08 GMT

Extra info to clearify the case

Datumindienst is in this Excel file the first day of an employment

Datumuitdiens is the last day of the employment, so no value in this fields means the employment is not yet ended.