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.
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?
