How do i create a Time Based Chart for this?

[#1]

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.

[Anonymous]

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

I hope this is helpfull.

Good Luck,
Dennis.

[#3]

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.

[Anonymous]

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.

[Anonymous]

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.