2 Replies Latest reply: Oct 20, 2017 10:29 AM by Abhijeet Mali RSS

    Calculated Dimension over a measure to use in a filter Item?

    Abhijeet Mali

      Hello Everyone,

       

      I need a help with creating a calculated dimension over 4 different measure to be used in a filter pane. And also mix and match these filters to investigate the data further.

       

      I have data similar to the following table -

          

      Customer NameYear     Total ImpactCategory 1 ImpactCategory 2 ImpactCategory 3 Impact
      A2017$82.1K$30.1K$27K$25K
      A2016$519.5K$190.3K$171K$158K
      A2015$641.1K$234.8K$211K$195K
      B2017$686.6K$251.5K$226K$209K
      B2016$461.9K$169.2K$152K$140K
      B2015$2,154.2K$789.1K$710K$655K

       

      The total impact column is the summation of  impacts across all three categories

       

      Sliding scale to create 'Impact' dimensions on all three measures is as follows -

       

      If the value is less than zero then Negative

      If the Value is equal to zero then Zero

      If the Value is greater than zero then Positive

       

      Initially I tried to create calculated dimension for category 1 impact as follows (to be used it in filter pane) -

       

      if([Category 1 Impact]<0 'Negative', if(Category 1 Impact) = 0, ' Zero', 'Positive')

       

      And followed the same logic for developing other 3 dimensions

       

      Which works fine when I wanted to see if any of the customers for all the years have at least once Negative, Positive or Zero Impact. Also all the filters work in combination with each other. However I did not wanted this functionality my intention was to create dimensions so that they work on aggregation of these measures across all the years by customers

       

      Hence, I created calculated dimension as follows (to use in the filter pane) -

       

      IF(Aggr(Sum([Category 1 Impact]), [Customer Name])<0 'Negative' IF(Aggr(Sum([Category 1 Impact]), [Customer Name]))= 0, 'Zero', 'Positive'))

       

      These calculated dimensions in a filter work fine when used individually, but do not work in conjunction (Initially applied filter is not being retained when I try to select a value from another Impact filter) with each other. My problem is I want to be able to mix and match these filters to derive further insights from the data.

       

      For example - Let's say I want to filter customers who had negative impact in category 2 but positive impact on category 1 and negative in category 3. but I am not able to do it with aggr filters

       

      Can anyone please help me with this problem?

       

      I am also attaching a sample QVF and Excel file.

       

      Thanks!