topic Re: background color dimension header pivot table in QlikView

background color dimension header pivot table

rudywelvaert — Wed, 18 Nov 2015 15:45:45 GMT

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

Re: background color dimension header pivot table

rwunderlich — Wed, 18 Nov 2015 16:41:06 GMT

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

-Rob

Re: background color dimension header pivot table

anlonghi2 — Wed, 18 Nov 2015 17:56:10 GMT

Hi Rudy,

please look at attached qvw.

I used the sample data in the attached excel.

Let me know.

Best regards

Andrea

Re: background color dimension header pivot table

rudywelvaert — Mon, 23 Nov 2015 13:27:21 GMT

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

Re: background color dimension header pivot table

rudywelvaert — Mon, 23 Nov 2015 13:33:07 GMT

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.