Fractile an Expression in a Pivot Table

Wed, 23 Sep 2015 13:36:44 GMT

Hi all,

I have created a Pivot table that is supposed to show a value in the cell and then be color coded based on the percentile that value is in.

The problem I am having is that when I add the expression to determine the fractile in the background color the pivot table expands to show all values for the dimensions rather than what I have selected. Here is the example of what I am dealing with:

The Pivot table has 3 dimensions. LastName and FirstName are the left two columns and Market is across the top in row 1.

The cell shows a score for each person in each market based on an expression in a variable: (=$(Score))

Then the background color expression is:

=if($(Score) >= $(Score_50), RGB(0,128,), RGB(255,0,0))

Then the variable expression for the variable Score_50 is (I shortened it to make it easier to understand):

fractile({1<[Market Radius]=P([Market Radius]),Market=P(Market)>} TOTAL <Market> aggr(nodistinct (

if(sum({1<[Market Radius]=P([Market Radius]),Market=P(Market)>}Past_Year_Sales)>50,3,1)

if(avg ({1<[Market Radius]=P([Market Radius]),Market=P(Market)>}Sales_Price)>100,5,1)

), LastName, Market), 0.5)

So the real issue is that even though I have selected to show 3 people in the pivot table (3 LastNames), the table shows all 50 people, but only has correct Scores for the people I have selected. What I need to know is what can I do to the Score_50 formula to make sure that the 50th percentile value is correct across each individual Market, but also so that it doesn't keep expanding my table.

I'm sorry if this is confusing to people, but I would much appreciate any help on this matter.

Thanks

topic Fractile an Expression in a Pivot Table in QlikView

Fractile an Expression in a Pivot Table