usernames, Date, MonthNames, QuarterNames, BonusType, ID
User1, 01/01/2011, Jan 2011, Jan-Mar 2011, a, 1
User1, 01/01/2011, Jan 2011, Jan-Mar 2011, a, 2
User1, 01/01/2011, Jan 2011, Jan-Mar 2011, a, 3
User1, 01/01/2011, Jan 2011, Jan-Mar 2011, b, 4
User1, 01/01/2011, Jan 2011, Jan-Mar 2011, b, 5
User1, 01/01/2011, Jan 2011, Jan-Mar 2011, c, 6
User1, 01/01/2011, Jan 2011, Jan-Mar 2011, c, 7
User1, 01/01/2011, Jan 2011, Jan-Mar 2011, c, 8
The second holds:
BonusType, TargetBand, TargetValue
a, 0, 0
a, 1, 2
a, 2, 4
b, 0, 0
b, 1, 7
b, 2, 15
c, 0, 0
c, 1, 10
c, 2, 20
I am trying to create a pivot table that shows the total value of bonus payable for each 'BonusType' in each Month based on following business rules.
- TargetBand 0 is payable when count of ID aggregated per month is <50% of a given target
- TargetBand 1 is payable when count of ID aggregated per month is >=50% <100% of a given target
- TargetBand 2 is payable when count of ID aggregated per month is >=100% of a given target
N.B. I can not code this is the script as I need to %'s above and the target value to be variable that can be changed by the end use so that they can see what would happen if the ranges where changed.
I can get an expression that will return the TargetBand for a given target, but when I try and use that in an expression to return TargetValue I get no results.
I have attached a sample QVD.
Message was edited by: jedgson
Edited the ID on the last 3 rows of the sample data to be 6,7,8 and not 6, 6, 6
If anyone can help with a nicer/better way then please let me know.
Thanks
Jay
EDIT:
Well at least I almost had it. When I collapse the Month names dimension it is giving the wrong value. Looks like it is multiplying the total count by the BonusValue for each BonusType and then summing them.
