1 Reply Latest reply: Jun 27, 2011 11:03 AM by James Edgson RSS

    PivotTable Expression

      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

          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)