Conditional formatting on LISS based on Collect() line item value
I have a financial reporting module with only one line item, Collect, which as the name suggests is collecting values from a LISS with the Collect() function. The LISS is a fake versions list that has actual, budget and actual vs. budget as list members. I now want to do conditional formatting to the "Actual vs. Budget" LISS list member so if the actual is below budget the numbers are red. I understand how you normally format line items but since I now want to format a specific list item based on the collect I'm not sure how to get this done since any rule I create for the Collect() line item will impact all list items.
For now, formatting the font itself is not yet available. But we can do format the background color of the cells.
As for your requirements, you can try creating a new line item (number format) applying the same LIS as dimension. Input your condition resulting to number (for eg. if actual lines < bgt lines then 1 else 0). Then use the new line as your conditional format base (eg. if the value is 1 then use the red bg).
Hope this helps or gives you idea on how to do it using your LIS.
I have successfully created the CF line item to conditional format based on 0 and 1 but cannot get this to the New UX without creating a Saved View which then blocks my filters... and I cannot seem to create the CF directly in the New UX based on my pivoting:
Re: Conditional formatting on LISS based on Collect() line item value
Hi @Akhtar.shahbaz , thanks for showing the formula works very well. However, how did you manage to get the CF to the New UX? When I create the grid card from Custom View it doesn't allow me to decide on CF, and if I select a Saved View I will not be able to do the filtering that I'd like.
Make sure when you want to apply conditional formatting on custom View then line item must be either in row or column then only you can apply CF, You can't apply CF if you keep line item on page selector.
Once you applied the CF then you can move your line item to Page selector, it will work
Thanks this initially worked but I'm now having issues. As I have fake versions also in the column I only want to CF the variances (ex. Actual vs. Budget, Forecast vs. Budget etc.) and not the single versions (Actuals, Budget, Forecast). Even though I only select the line items under the variance versions and try to submit CF for only them it goes for all anyways... Do you know how to resolve this?