Show value (string) based on Variable

[#1]

Dear sirs,

I Have a table like this:

Table:
BudgetVersion Account Amount Comment
BU-1 100 50 Last year + 3%
BU-1 200 10 According to seperate calc
BU-2 100 55 Wild guess
BU-2 200 14 Based on sales dept. estimate

I a pivot table, i have a column with the COMMENT field (text string) among other columns. I want to ONLY have the comment that relates to budgetversion BU-2 shown. I have a variable vActualBudget that contains the current budgetversion (in this case BU-2)

Can I use a clever set-analysis or do I need to use pick/match/fieldindex or other functions? Selecting the actual budgetversion from a listbox is NOT an option.

[brenner_martina · Accepted solution]

Hi Jesper,

Is the problem, that you have 2 (or more) entries in the Field Comment but only one BudgetVersion?

Do you want to show in the commentline only the BudgetVersion BU-2? That would be easy:

expression in Comment: If(BudgetVersion='BU-2',Comment)

[#3]

Hi,

The problem WAS, that I use ACCOUNT as a dimension in a pivot table.

So, I have two field values in COMMENT that matches the ACCOUNT, but I only want to display one of them, the one that matches the variable.

I can handle the amounts by a set analysis sum function, but i didnt know how to handle the string, since the ordinary calculating formulas (sum, count aso.) didnt support string values.

But i tried = only({$ < BudgetVersion = {"$(vActualBudget)"} > } COMMENT

And it worked.

But thanks anyway 🙂