Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
StefanSonntag
Contributor II
Contributor II

How to calculate formulas stored in a column?

Imagine you have a data island table with two columns, e.g. "Metric" and "Function". The function column contains a syntactically correct formula per metric which calculates its result over the remaining tables of the data model. Then create a table object in the UI and have Metric as dimension and the expression "$(=[Function])" as measure column. The output is an empty measure column unless I select one of the metrics in the dimension columns, then Qlik Sense Enterprise SaaS shows the correct result for the chosen metric. How do I need to formulate the expression so that it calculates the results for all rows right away? An empty sample QVF using the data model generated by hitting CTRL-0-0 in the load editor is attached

Thanks,

Stefan
Labels (2)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

This doesn't work because $ expansion happens outside the chart. That means when you have multiple measures in scope (not selected and reduced to one - which you see works), the $ expansion can't produce multiple row-wise expressions (which is desired). As an alternative, you could try with pick(match(... Something like:

Pick(Match(Metric,'A', 'B', 'C'),$(=Only({<Metric={A}>}Formula)),$(=Only({<Metric={B}>}Formula)),$(=Only({<Metric={C}>}Formula)))

tresesco_0-1632290492491.png

 And to make it dynamic, try using Concat() to produce the expression.

View solution in original post

2 Replies
tresesco
MVP
MVP

This doesn't work because $ expansion happens outside the chart. That means when you have multiple measures in scope (not selected and reduced to one - which you see works), the $ expansion can't produce multiple row-wise expressions (which is desired). As an alternative, you could try with pick(match(... Something like:

Pick(Match(Metric,'A', 'B', 'C'),$(=Only({<Metric={A}>}Formula)),$(=Only({<Metric={B}>}Formula)),$(=Only({<Metric={C}>}Formula)))

tresesco_0-1632290492491.png

 And to make it dynamic, try using Concat() to produce the expression.

View solution in original post

StefanSonntag
Contributor II
Contributor II
Author

Thanks  @tresesco that was exactly the problem, your solution works perfectly and dynamically using concat().  Do you happen to know the max length of the concatenated string?

Thanks,

Stefan