Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
jedgson
Creator
Creator

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

1 Reply
jedgson
Creator
Creator
Author

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)