Teemu - Good job and I think you got it working by using FindOneOf() function.
Chester - This is interesting now and it is challenging at the same time. I have got it working using Aggr() and Set Analysis. I have added one more field to see how my expression behaves. I have done this assuming that you wanted to implement this in real world application with complex data model. I have also improvised my previous expression and here is what you need :
__________________________________________________________
__________________________________________________________
Solution 1:
Expression :
IF(
$(=vMyCondition) = -1, White(),
IF(SUM({<Product = {$(=CONCAT(Distinct Product,',')) } >} TOTAL <Product> Sales) > 0 AND GetSelectedCount(Product) = 0, White(),
IF(SUM({<Product = {$(=CONCAT(Distinct Product,','))} >} TOTAL <Product> Sales) > 0 AND GetSelectedCount(Product) > 0, Green()
, QlikTechGray())
))
Explanation :
- First IF conditional statement is evaluating if there is any selection on any fields in my data model and if there is no selection at all then use White() colour
- Second statement checks two conditons : (1) It checks if selection count on Product field is zero, which is GetSelectedCount(Product) = 0 and (2) Using Set Analyis I'm restricting only possible values of Product field. And if both the conditions are true then use White() colour
- Third statement works as second statment but it checks if there is any selection count on Product field which is greater than zero (GetSelectedCount(Product) > 0,). If this condition is true then it means there is selection on Product field, hence I'm using Green() colour
- Finally, if all the above condtions are false then use QlikTechGray() colour
__________________________________________________________
__________________________________________________________
Solution 2:
Taking this a step further where I'm also controlling Year Dimension in my Line Chart. For example, if I select value = 20 on Sales field then I wanted to see Product {A,B} in White() colour and at the sametime we wanted to see Year = {1} in QlikTechGray() colour because it is excluded value within Year field. But we wanted to see Year={2,3} in White() colour because they are possible values for this field. I hope this makes sense.
Expression :
IF(
$(=vMyCondition) = -1, White(),
IF(Aggr(SUM({<Product = {$(=CONCAT(Distinct Product,','))}>} Sales), $(=vAllDimensions)) > 0 AND GetSelectedCount(Product) = 0, White(),
IF(Aggr(SUM({<Product = {$(=CONCAT(Distinct Product,','))}>} Sales), $(=vAllDimensions)) > 0 AND GetSelectedCount(Product) > 0, Green(),
IF(Aggr(SUM({<Product = {$(=CONCAT(Distinct Product,','))}>} Sales), $(=vAllDimensions)) > 0 AND GetSelectedCount(Product) = 0 AND GetSelectedCount(Year) > 0, White()
, QlikTechGray())
)))
This expression works very similar to first solution but it's still sensitive on Year field.
__________________________________________________________
__________________________________________________________
I hope all this makes sense. Please let me know if you have any questions.
Note : We can take this one more step further by writing generic expressions which works on any Dimension or any QlikView document. But I'm not going to do it unless I'm re-using this functionality/feature in many applications.
Thanks again for asking this question and appreciate for providing detailed explanation with example.
Cheers,
DV
www.QlikShare.com
____________________________________________________________________________________________
.png "Luminary Alumni"){kind=icon}
