1 Reply Latest reply: Jun 20, 2014 12:39 PM by Boris Tyukin RSS

    Scoring

    David Young

      I know I have said this before but I am still learning Qlikview and the responses I have received from the Community have been awesome.  I have the following fields in my application:

       

      1.  Organization - Name of Client 

      2.  Student - Number of Students    

      3.  WOID - Work Order Number

      4.  CreatedDate - Date Work Order is Created

      5.  TrnsDate - Date of Transaction

      6.  Hours - Number of Hours Work

      7.  PurchaseDate - Product Purchase Date

       

      What I want to build is a Score Card for my Clients over time with the following criteria:

      1.  One point WOID/Student/Per Year >=.70

      2.  One point if the Number of Hours on Work Orders is >=.60

      3.  One point if both 1 and 2 reached.

       

      So my clients can get 0 - 2 points.  I would like for it to be a rolling 12 month so output would look like this:

       

      Client A

      Purchase Date - 8/1/2009

      8/1/2009 - 6/20/2010 - 0 points  (Just getting starting and not enough data yet)

      6/20/2010 - 6/20/2011 - 1 point (One point because they reached .70 from above scale)

      6/20/2011 - 6/20/2012 - 1 point (One point because they maintained .70)

      6/20/2012 - 6/20/2013 - 1 points (One point because they maintained .70)

      6/20/2013 - Today - 2 points (The reached both .70 and .60 from above scale)

       

      Here is where my issue is.  I can create a formula that will run on a single 12 month period. Copy that information off to a separate file.  The change the period, copy that information off to a file.  Keep doing this over and over again until I get through all my periods.  That is really time consuming and was wondering if someone could point me in the direction so I only have to run the application once and get all the points at once.  Keep in mind this is just one client.  I have over 4000 clients with different purchase dates going back to 2004.  Any help would be awesome.  I hope this makes sense.

       

      David