PivotTable Expression

jedgson — Mon, 27 Jun 2011 13:44:52 GMT

Hi,

I have 2 tables.

The first holds:

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

Re: PivotTable Expression

jedgson — Mon, 27 Jun 2011 14:55:33 GMT

Figured it out.

Its not the tidiest but here we go:

If( $(eMonthlyPercent) >= 0.5 and

$(eMonthlyPercent) < 1

, Sum(

If( TargetBand = 1

, TargetValue

, 0

)

, If( $(eMonthlyPercent) >= 1

, Sum(

If( TargetBand = 1

, TargetValue

, 0

)

, 0

)

) * Count( Distinct ID)

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.

ie: (2*8) + (7*8) + (10*8)

Should be doing (2*3) + (7*2) + (10*3)

topic PivotTable Expression in QlikView

PivotTable Expression

Re: PivotTable Expression