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 Name | Year | Total Impact | Category 1 Impact | Category 2 Impact | Category 3 Impact |
| A | 2017 | $82.1K | $30.1K | $27K | $25K |
| A | 2016 | $519.5K | $190.3K | $171K | $158K |
| A | 2015 | $641.1K | $234.8K | $211K | $195K |
| B | 2017 | $686.6K | $251.5K | $226K | $209K |
| B | 2016 | $461.9K | $169.2K | $152K | $140K |
| B | 2015 | $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!
