Can I use an expression as the “fieldname” in a Set Modifier? In “<x={y}>” can “x” be an expression?
I always believed the answer to be “No”, x may only be a field name. That understanding is reinforced by what I read in the help doc for “set modifier”.
Yesterday I was surprised to discover that in Qlik Sense, this is a valid expression that returns sales for year 2015.
sum({<"=Year(OrderDate)"={2015}>}Sales)
This also works:
sum({<"=left(Country)"={'M'}>}Sales)
This is all news to me. And very interesting.
I stumbled across this accidentally when using the Expression Editor > Set Analysis > Insert button. I had selections in the derived field OrderDate.autocalendar.Year field. The set generated by the Insert tool was:
{<"=Dual(Year([OrderDate]),YearStart([OrderDate]))"={'2014','2015'}>}
That expression is the derived field definition that was specified in the script.
I have not yet formulated an opinion as to whether this is useful, or if there are any cautions or limitations when using. I’m at the curious stage at this point and will look into it more as well as read comments I am sure will follow.
-Rob
Thanks Rob! Learning new things everytime I read your blog blog. Will do more analysis on this.
Vow! Thanks for sharing this. I will surely check on this.
Nice secret of Set Analysis revealed. Did not know about this either. Thank you for sharing!
I could see this coming in handy in a pinch. Good discovery!
Uau !
Brilliant
You are my Qhero ! (again)
Sempre fi,
Cotiso
Nice find Rob!! Would be super curious to know how it performs. Thanks for sharing.
Surprised!
I think, resource consumption will be higher for this expression because of double validation for each row.
Just a thought. It might work smooth because of set analysis.
Regards,
Nilesh
I just tried it with QV 12.5 and it didn’t work. Means the expression returned further a result but doesn’t consider these set statements and just ignoring them like specifying a not existing field-name or a field without any valid value. I tried both mentioned approaches:
sum({} [Value])
in various ways and they are always ignored.
Further I couldn’t imagine how this might technically working because a set analysis set a selection state for a field-value to TRUE or FALSE and here is no field against a condition could be evaluated. By the newer feature of derived fields it may be different because they aren’t real fields else another abstract layer which may be resolved in a virtual table before the set analysis is applied.
Maybe someone could test it, too and also with derived fields. In QlikView the derived fields aren’t implemented and therefore I couldn’t test it – especially not the performance impact against a native table/field.
– Marcus
Unfortunately the expression was shrinked and therefore I try it again by replacing the lesser/greater chars with #:
sum({# “=Year(Datum)” = {‘2022’}, “left([Gebiet], 1)” = {1}, [KPI] = {‘IST’}#} [Value])
In QV you can use:
sum({# Datum = {‘=Year(Datum) = 2022’}, Gebiet = {‘=left(Gebiet, 1) = 1’}, [KPI] = {‘IST’}#} [Value])
Yes, I know. On the right side expressions/variables could be used to define a search-string with or without wildcards/operators. But this isn’t the topic of this blog-posting else could an expression be used on the left side and if would it create a benefit and/or might it have any side-effects?
Beside this a statement like:
Datum = {“=Year(Datum) = 2022”}
isn’t a search-string else a boolean check and is AFAIK not evaluated against the field on a column-level else the check is performed on a row-level. This means it’s not a real set analysis else an if-loop included within the set analysis syntax and quite the same like:
if(Year(Datum) = 2022, then, else)
– Marcus
it would be interesting to know where it can be used
Thanks Rob! This a Great News for a Qlik Developer!
Unfortunatly I noticed that If I use an expression with an “Aggr Dinamic Dimension” in the left part of set analysis, the formula doesn’t work!
ex.
Sum({#”=Aggr(Sum(Value),Brand)”={[>=8000]}#}Value)
Obviously I can use the right part of Set Analsysis to fix it
Sum({#Brand={`=Sum(Value)>=8000`}#}Value)
# = lesser/greater chars
— Dario