Hi,
I have a requirement to create a visual with 2 rows and multiple columns (Each column represent different metrics). Where To row is the value at lowest granularity level and second row is for select any higher granularity level based on the given options. Ie, We will be having a benchmark filter where we can select any higher granularity level, based on the selection, The second row will display the metrics at selected granularity level. And also another ask was to color code the Metrics at top row by comparing the values with highest granularity level. Ie, for a metric ‘Time TO Hire’, If store level value is > National/Field Office/CoOp Level, then store level Time to hire should be in GREEN Color, else it will be in Red color.
To achieve this we came up with an approach, where we will have all the metrics as columns and All aggregation levels as rows. So I have created 2 tables and placed them together. One is only for store level and another is for selected benchmark level. Now I need to color code the store level based on selected benchmark level if its higher then green else red. How can I achieve this. Can anyone Help?
Hello @Ganga, I think we can make this work. What you should do is create a calculated field that will check if the value is greater than the value for the hierarchy you have selected. You would then need to return integer value, so that you can utilize it properly in the conditional formatting. Here is what the first field would look like:
Greater Than Hierarchy =
Depending on how your metrics are calculated, you may need to switch between sumOver, countOver, minOver, etc, but this allows you to partition the value based on each hierarchy level and compare.
Now, you can use this calculation in your conditional formatting, rather than the field you are formatting by. I’ll show you the example below:
Notice as well, that I used your benchmark field as the partition field for the comparison metric. Using this, you should be able to get the result you are expecting. I am going to mark this as the solution, but please let me know if you have any follow-up questions. Thank you!
This method I have tried and was not able to implement it properly. Because, The Store level metric and Benchmark one is not a single field. If u see below snapshot All levels of aggregations are inside a single column, from there I filtered only for store level in first row ,and combined other levels from the same column in to a parameter called benchmark.
Here the Store level and other levels are appearing as rows in my dataset. Rows highlighted in green are part of benchmark parameter.
Here store level is 10 and National Level(selected benchmark) is 5 ,so the expectation is flag should return 1 and Green color. But its not seems correct.
Hello @Ganga, I see the issue. If you have a calculated field that will only returning the time to hire value at a store level, then you do not want to include that as the partition, but rather the metric being summed. Your calculation would look like this instead:
ifelse(
sumOver({Store Level Time_to_hire}, [], PRE_AGG) > sumOver({BenchmarkLevel Time to hire}, [], PRE)AGG), 1,
0,
)
You would have to create this for each metric, but I believe this should resolve the issue you are facing.
