background color dimension header pivot table

[rudywelvaert]

Hi,

How can the background color of the header of a dimension in a pivot table be changed according if one or more of the aggregated values along the other dimensions meets a certain criteria

e.g.

Month	Customer manager	John	Annie	Charles	Maria	Peter	Total
Aug 2014		20.912	34.909	41.260	31.829	54.049	203.704
Sep 2015		24.595	43.754	12.394	62.606	33.875	217.224
Oct 2015		38.706	55.654	26.034	23.341	52.395	175.385
Total		84.213	134.317	119.688	117.776	140.319	596.313

The criterium is value >= 50.000

In the aggregated values I use this for the background color:

=If ((Dimensionality() <> 0) and (SecondaryDimensionality() <> 0),If(Sum({<Month_From={"<=$(=Max(Month_From)) >=$(=AddMonths(Max(Month_From), -2))"}>} If((Month_From = Month_From _STARTDATE), OPEN_AMOUNT)) >= 50000, Pink()))

When I apply this formula also to the dimension Customer manager I get this result:

Month	Customer manager	John	Annie	Charles	Maria	Peter	Total
Aug 2014		20.912	34.909	41.260	31.829	54.049	203.704
Sep 2015		24.595	43.754	12.394	62.606	33.875	217.224
Oct 2015		38.706	55.654	26.034	23.341	52.395	175.385
Total		84.213	134.317	119.688	117.776	140.319	596.313

Only the Customer manager for whom the first value meets the criterium, gets the correct background color.

The Custom managers who have a value that meets the criterium in other values of the dimention Month, don't get the correct background color.

Tried to use the Agg() function over dimension Month, but Custom manager got a background color.

=If ((Dimensionality() <> 0) and (SecondaryDimensionality() <> 0),If(Max(Agg(Sum({<Month_From={"<=$(=Max(Month_From)) >=$(=AddMonths(Max(Month_From), -2))"}>} If((Month_From = Month_From _STARTDATE), OPEN_AMOUNT)),Month_From)) >= 50000, Pink()))

Any ideas?

Thanks

RW

[rwunderlich]

I think you need use both the MonthFrom and CustomerManager dimensions in your aggr.

-Rob

[anlonghi2]

Hi Rudy,

please look at attached qvw.

I used the sample data in the attached excel.

Let me know.

Best regards

Andrea

[rudywelvaert]

Thanks Rob

Do you mean this?

=If ((Dimensionality() <> 0) and (SecondaryDimensionality() <> 0),If(Max(Aggr(Sum({<Month_From={"<=$(=Max(Month_From)) >=$(=AddMonths(Max(Month_From), -2))"}>} If((Month_From = Month_From _STARTDATE), OPEN_AMOUNT)),Month_From,Customer_manager)) >= 50000, Pink()))

Because this doesn't work either

RW

[rudywelvaert]

Grazie, Thanks,

In reality the case is a bit more complicated. The number of months shown, is variable (with a slider).

So aggregating with Max over a number of months can not be done before in the the script.

I can only make a table with total per Month_From and per Customer_manager.

The Maximum must be applied in the expression for the header color.

I haven't found it yet.

RW