Scoring

Fri, 20 Jun 2014 13:41:50 GMT

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

Re: Scoring

Anonymous — Fri, 20 Jun 2014 16:39:57 GMT

Hi David,

looks like a nice little challenge. Can you share your QVW? I understand it, you want to build dynamic calculations based on a time period selected and a client?

topic Scoring in QlikView

Scoring

Re: Scoring