Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a pivot table with a calculated dimension consisting of the following formula. Essentially, it forecasts a completion date based upon how long a particular job has been in process and factors in weekends. If the forecasted completion date is a Sat, it adds 2 days. Sun, 1 day so that the forecasted date is a Mon in both cases. The Fcst Station field is the forecasted completion date of the current operation. The Fcst Order Comp date is the estimated completion date of all remaining operations.
- =IF(WeekDay(DATE(ProcessDaysTotal-(TODAY()-JO.Start_Date) + TODAY())) = 'Sat',
DATE(((ProcessDaysTotal+2)-(TODAY()-JO.Start_Date)) + TODAY()),
IF(WeekDay(DATE(ProcessDaysTotal-(TODAY()-JO.Start_Date) + TODAY())) = 'Sun',
DATE(((ProcessDaysTotal+1)-(TODAY()-JO.Start_Date)) + TODAY()),
DATE((ProcessDaysTotal-(TODAY()-JO.Start_Date)) + TODAY())))
That formula seems to work fine. The next part is where I seem to be getting inconsistent behavior from QV. In the Background color formula for this particular calculated dimension, I want to to determine if the forecasted completion date falls before or after the actual due date. If it will be late, I want the background color to turn red, otherwise remain unchanged. It works on a few of the rows, but not all. However, if I select a specific row which does meet the condition, the background color changes... I don't understand why.
Here is the Background Color formula in question:
- =IF(IF(WeekDay(DATE(ProcessDaysTotal-(TODAY()-JO.Start_Date) + TODAY())) = 'Sat',
DATE(((ProcessDaysTotal+2)-(TODAY()-JO.Start_Date)) + TODAY()),
IF(WeekDay(DATE(ProcessDaysTotal-(TODAY()-JO.Start_Date) + TODAY())) = 'Sun',
DATE(((ProcessDaysTotal+1)-(TODAY()-JO.Start_Date)) + TODAY()),
DATE((ProcessDaysTotal-(TODAY()-JO.Start_Date)) + TODAY()))) >= SRL.Due_Date,RGB(255,0,0))
In this example, both Fcst Order Comp cells should be red, yet only the shown cell is red.
If I select the second row manually, specifically the PJ.Job_No field, it then turns red as well.
Why doesn't it turn red automatically from the beginning?
Thanks for your help, and I'd be happy to hear anyone's ideas on a better way to do this. Is it possible to reference and calculated dimension as the trigger for conditions somehow?
Thanks,
Rob