Skip to Content

Webi - overwrite dimension

Dec 05, 2017 at 06:10 PM


avatar image

I have this really funky database in webi and I'm trying to overwrite a dimension to the value of a record with a common dimension. (i can do this line-by line, but i can't seem to get totals this way) .three variables - facility ID (common dimension), amount (measure), risk code (dimension to overwrite).

the way my database is setup is that there can be multiple records for a single facility ID, but if the amountfield has a value, the risk code field comes up "NA" (not NULL), if amount is 0 then there's a value in the risk code field. i need these these NA's to become whatever the risk code is for the matching facility ID that isn't NA. if any of that makes sense.

i made a custom variable that sets NA values to 0 then gets the max Risk Code

=Max(If([Risk Code]="NA";"0";[Risk Code]))

the max function turns my dimenion into a measure, but the dimension is actually numerical values anyway so doesn't really matter. it works great when i have a table with each Facility ID line item, but if i try to make a table that sums the amounts of each risk code, i get a multivalue error.

Assuming i need to add some kind of In([Facility ID]) to my formula, but when i do that, it doesn't work. Could i use a UseMerged function or something like that? I'm completely lost. Any help would be greatly appreciated. Thanks!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Dec 05, 2017 at 06:12 PM

Can you share some sample data.

Other thing i have noticed you have used double quotes for replacing na with 0

Correct one is

=Max(If([Risk Code]="NA";0;(Risk Code]))

10 |10000 characters needed characters left characters exceeded