Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
got 2 tables T1, and T2
T1 has two fields called NAME and Expression
under NAME there are names of fields in another table:
NAME Expression
-------------------------
sales sum(sales)
returns sum({<product={P1,P2}>}returns)
in T2 there are 2 fields named sales, and returns along with other fields
what I want to accomplish in a table is the following:
add dimension NAME, Expression, and Measure
so the output would be:
SALES, SUM(SALES), 123456
RETURNS, sum({<product={P1,P2}>}returns), -456732
Kindly advise on how to accomplish this
You can calculate this in a KPI like this:
returns
=$(=Only({<NAME={returns}>}Expression))
Hello @Lisa_P
thank you for your reply
but this only evaluates one expression
what I want is to evalute each expression as I put them in a table
so each row should give me a number next to each NAME
Such approach isn't possible because your expression remains a string and won't be evaluated (unless with the above shown $-sign expansion which creates an adhoc-variable for just a single value/expression).
The only way to apply different expressions for each row is the use of a conditional logic like:
pick(match(Name, 'x', 'y', 'z'), sum(Field), avg(Field),max(Field))
this approach only evaluates on expression
Field Expression value
Field1 max(Field1) 800
Field2 sum(Field2) 1500
...
I have Field and Expression in the schema
next to each Field and Expression I want to compute / evaluate the Expression and display the value
it's just an example to show the needed logic for such an object. Most users which want to apply such an approach just use it like above by writing the dimension-value to expression matching manually. It's quite simply and will work well - unless by larger data-sets because it's the opposite of an optimized way to calculate data.
Nevertheless this doesn't mean that the dimensions and expressions couldn't be maintained within a table - but like mentioned you couldn't use it directly else you would need to aggregate the dimensions and also the expressions per concat() within single strings which could be afterwards evaluated with the above shown $-sign expansion. It's not trivial because you need to synchronize both listings - maybe with a rowno() in the load as sorting-parameter - and probably more tricky to consider the various kinds of quotes and brackets within the multiple list-values and by aggregating them and calling the resulting adhoc-variables.
IMO it's a rather bad way to design UI views and I couldn't recommend it - even if it's technically possible.
Maybe this is an approach that you can use ...
https://community.qlik.com/t5/Design/Qlik-Sense-Custom-Report/ba-p/1471797