1 Reply Latest reply on Sep 23, 2016 2:15 PM by Yuriy Fal

    Convert columns to values in a new field and show correct pivot values in a crosstab

    Reesha Santhosh

      Hi all,

       

      I am looking for some help in converting some of the columns in my data to values in a dimension. Please find attached a sample workbook with what I have so far. I have also added the original data to the workbook for reference.

      If you click on the original data in the dashboard, you can see 16 dimensions that have their names as *_Goal, *_Actual, *_Status or *_Bench. I need the  *  in these dimension names to be values of a new column, which I am able to get by using the Pivot option and a calculated field - 'Metric Name'. I created 4 new calculated fields (Goal, Actual, Bench and Status) to map the correct pivot values but something seems to be wrong as I am not getting the correct values in a cross-tab. You can see on Sheet 2 that 'Goal' has an extra 'Null' value and all the values of the field 'Actual' map to this Null value. So, excluding the Null doesn't help. What I have on Sheet 1 shows correct values when I hover over the green shapes, but that is not the final representation I am looking at.

       

      My sheet should finally look like below - an example row added. It's probably something simple I'm missing here. Please help.

       

      NameIDQuarterServiceCategoryMetric NameGoalActualBenchStatus
      RP121Q4 FY2015ARLevelR1007584*YELLOW