Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a calculated dimension in a pivot table and I am trying to highlight the 'actual' column if it's greater than the 'projected' column.
The actual calculation is working fine, but I can't get the background color to work properly. I have a feeling I made a stupid mistake but I can't get this last detail working.
Projected dimension is a static value related to the primary dimension Location.
Actual: =aggr(sum(ActualDays),Location)
Background Color: =if((aggr(sum(ActualDays),Location))>ProjectedDays,LightRed())
Desired Result (background instead of text color):
Locations | Projected | Actual | Month | 8/1/2017 | 7/1/2017 |
---|---|---|---|---|---|
Location A | 315 | 410 | 1 | 31 | |
Location B | 829 | 619 | 7 | 208 | |
Location C | 312 | 1835 | 1 | 31 |
Current State:
Thank you
For me, you don't have to use aggr() in your pivot chart. sum(Actual) and sum(Projected) are enough. And for the text color =if(sum(Actual)>sum(Projected), red())
Each location has a static projected days number that is totally separate from other location projections. The actual days also link directly to each location which is why I tried the aggr direction. It just seems strange that the aggr Actual column calculation does not work when using it in an if statement with the background color, but it does work in a calculated dimension.
Thank you
did you try like this?
=if( Sum(aggr(sum(ActualDays),Location)) > ProjectedDays , LightRed() )
Hi Philip,
Can u share sample script?
Thank you
Dimension->Used Dimension->Select Actual->Click on + Symbol->Background Color-> Write your Expression
This was very helpful for me.
Thank You for Sharing