Skip to main content
Contributor
Aug 27, 2024 10:45 PM UTC
Solved

Conditional Formatting to Show "0"

Hello,

I'm building a cube view and we have some small amounts that are causing our B/W Pcts to have a huge %. Is there a way I can use conditional formatting to make the value show as 0 instead?

WillVitale_0-1724798723948.png

Thanks,

Will

Member
Aug 28, 2024 7:17 AM UTC

Hi Will, you can use e.g. a U8# member and put a dynamic calc on it to return 0. In the formula you can also define for which cases / amount ranges you want to show 0 (e.g. < 0.01 or > 0.01). This can alos be used to show 0 in case of NODATA.

Dim dAmount As Decimal = Api.Data.GetDataCell(your POV:U8#None).CellAmount If dAmount < 0.1 or Then Return 0 End If

7 replies

Member
Aug 28, 2024 7:17 AM UTC

Hi Will, you can use e.g. a U8# member and put a dynamic calc on it to return 0. In the formula you can also define for which cases / amount ranges you want to show 0 (e.g. < 0.01 or > 0.01). This can alos be used to show 0 in case of NODATA.

Dim dAmount As Decimal = Api.Data.GetDataCell(your POV:U8#None).CellAmount If dAmount < 0.1 or Then Return 0 End If

Contributor
Aug 28, 2024 3:25 PM UTC

Hi Matthias,

Currently my B/W column is already a UD8, so would the best thing is to create a new one or just edit the current one?

Member
Aug 29, 2024 6:45 AM UTC

Correct, I'd suggest to update the formula on that UD8# member to check the calculated value and adjust it (round or set to 0) before returning it.  

Contributor
Aug 28, 2024 11:44 AM UTC

I think you may have to change the formula to round off values to 0. Even if conditional formatting is able to make it zero, it would not impact calculation as it would still go ahead and calculate large percentages using the small amounts you are getting. 

Contributor
Aug 28, 2024 3:27 PM UTC

Hi,

I didn't know if you could use conditional formatting on the B/W % column that looks at a different column, but since I'm using a UD8 for it, I don't know if that's possible within a cube view.

Member
Aug 29, 2024 6:57 AM UTC

Standard formatting (also conditional) will always be applied to the current column/row based on the data in that column/row. It does not matter if the data is comming from a POV in the cube or from a calculation in the cubeview.

If think referring in conditional formating to another column will only be possible using paramters in combination with business rules. Maybe someone has another idea...