2 Replies Latest reply: Jun 24, 2010 11:30 AM by Jesper Juul RSS

    Show value (string) based on Variable

      Dear sirs,

      I Have a table like this:

      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.

        • AW:Show value (string) based on Variable
          Martina Brenner

          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)

            • AW:Show value (string) based on Variable


              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 :-)