4 Replies Latest reply: Dec 22, 2011 1:37 PM by Dennis Hoogenboom RSS

    How do i create a Time Based Chart for this?

      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?
          Dennis Hoogenboom

          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?

              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?
                  Dennis Hoogenboom

                  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.