08-14-2023 10:17 AM - last edited on 08-15-2023 04:19 AM by JackLacava
Hi All,
For a Bi Viewer Dashboard I use a Cube View which calculates values with a GetDataCell calculation. I display the values in a line chart. The values are displayed correctly, but the calculated variable name in the legend does not match the defined name in the Cube View calculation, instead the first used account name from the calculation is used.
How can I use the name I defined in the cube view cell in BI Viewer, or if it is not possible, how can I rename the variable name manually in BI Viewer?
Solved! Go to Solution.
08-15-2023 10:52 AM - edited 08-15-2023 10:59 AM
If I understand correctly, you'd want those records to be marked "COGS %", is that right?
The easiest way to go about that is to create a Calculated Field in Bi Viewer, by right-clicking on the datasource name:
In the Expression Editor that comes up, you just enter the name you want, surrounded by single-quotes:
That will return the value 'My Name' in that field for all records. If you need more complex logic, you can implement it with a more complex expression, like IIF etc. For example, to replace an account name, you'd use Replace([Account], 'MyWrongAccount', 'COGS %')
08-15-2023 03:51 AM
Hey Colin,
could you post the CV calculation definition please? And are you using the CV Data Adapter or the CV MD one?
08-15-2023 04:07 AM
Hi Jack,
this is my CV calculation: GetDataCell(Divide((A#GP - A#Sales), A#Sales)):Name(COGS %)
And I´m using the CV MD Command Type.
08-15-2023 10:52 AM - edited 08-15-2023 10:59 AM
If I understand correctly, you'd want those records to be marked "COGS %", is that right?
The easiest way to go about that is to create a Calculated Field in Bi Viewer, by right-clicking on the datasource name:
In the Expression Editor that comes up, you just enter the name you want, surrounded by single-quotes:
That will return the value 'My Name' in that field for all records. If you need more complex logic, you can implement it with a more complex expression, like IIF etc. For example, to replace an account name, you'd use Replace([Account], 'MyWrongAccount', 'COGS %')
08-16-2023 03:38 AM
Yes the Replace function solved the problem, thank you Jack!
08-16-2023 06:11 AM
Is it possible to use more than one replace function in a calculated field? If so how do I do that, 'AND' and a comma(,) did not work as separators between two replace functions.
08-16-2023 11:02 AM - edited 08-16-2023 11:03 AM
I suspect the logic you're after is something like "if we have name X in the record, replace it with A; if we have Y, replace it with B; otherwise, leave it as it was". You can achieve this by nesting IIF calls.
Iif( Contains( [Account], 'SomeName'),
Replace([Account], 'SomeName', 'FirstReplacement'),
iif( Contains([Account], 'SomeOtherName'),
Replace([Account], 'SomeOtherName', 'SecondReplacement'),
[Account]
)
)
Note that Replace is necessary only if you want to replace part of the original text; if you simply want to wipe what was there before, you can just return the value directly, i.e.
IIF( Contains( [Account], 'SomeName'),
'New Value',
IIF( Contains( [Account], 'SomeOtherName'),
'Another Value',
[Account]
)
)
08-17-2023 03:35 AM
Thank you again, it worked!
01-25-2024 01:22 PM
I was able to use the multiple replace function also Jack, Thank you!