Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, everybody!
My problem:
I have a list of Metrics and formulas for each metric in the excel file. Formulas from this file contain only QV operators and work properly. Something like this:
Metric | Formula |
---|---|
a | Sum({$<[Type] = {1},Metric = {'A'}>}Amount) |
b | Sum({$<[Type] = {1},Metric = {'B'}>}Amount) |
c | Sum({$<[Type] = {1},Metric = {'B'}>}Amount)/Sum({$<[Type] = {1},Metric = {'A'}>}Amount) |
And I have a chart 'straight table', where I should display volumes of metrics. It should be something like this:
Metric Name | Volume |
---|---|
A | 100500 |
B | 809 |
C | 568 |
What I have done:
I loaded metrics and formulas from excel. I picked Metric as a dimension, I picked Formula as an expression. And i got perfect list of the metrics and formulas .
I tried to use $(=Only(Formula)), but it works only if i picked one metric from a list (I use it in other tables, where I should calculate volumes only for selected metric).
What I whant:
I want to get volumes of all metrics from the list calculated according to the formulas from the exlel file in one list in straight table.
I have a lot of metrics, so the solution couldn`t be based on the IF(Metric = a, formula a..) or Pick().
How can I convert text to the calculated formula?
Hello, Jonathan!
thnx for your solution!
I loaded a list of formulas, but unfortunately I can`t got a value for metric yet.
I loaded
Concat (Formula, ',', Sort_Order) as FormulaList
created variable:
let vFormula = 'pick (Sort-order, '& Peek ('FormulaList')&')'
but I can`t get result with $(vFormula) in the chart..
Greg,
Can you please explain the expression with peek you have used.
I have a table field with the formulas (as they would work in QV) and a table field of order (ie ORDINAMENTO)that I want to be appeared in the report.
I use the trick that AUNEZ FABRICE proposed with the concatenation of all formulas to one value (using commas) and the pick expression in order to define on each row of my straight table which formula to run. Instead of row() I found it better to use the ORDINAMENTO field.
The formula field is simple set analysis which, for example, calculates the total sales (Sales + Other Revenue)
Greg
Alena
Make sure that Sort-Order is a dimension in the chart, or is uniquely associated (1:1) with a dimension in the chart. The expression needs a value of Sort-Order.
You can access the individual formulas like this (eg in a text box, to show formula #107):
=Only({<Sort-Order = {107}>} $(Formula))
I packaged this into a parameterized macro defined like this :
mExpr = Only({<Sort-Order = {$1}>} $(Formula))
Then I can use it in a textbox like this:
$(mExpr(107))
Regards
Jonathan
Here are some examples of the formulas I use this way:
Sum({<FNoteNumber={"A040201*"}, Cost_Centre_Code={9808}, PortfolioCode={'JOBX1'}>} Exposure) / 1000
Sum({<FNoteNumber={"A040201*"}, Cost_Centre_Code={9804, 9807, 0522}>} Exposure) / 1000
Sum({<Exco.Group={'S'}, FNoteNumber={"A04*"}, CUSIP={$(xSelBucket)}>} Exposure) / 1000
The macro $(mExpr(nn)) described in my previous post is also useful for testing individual expressions.
The pick() expression that you create must be a properly parseable Qlikview expression. If any one of the formulas has an error (such as a missing bracket or comma or function parameter error), then the whole pick expression fails. It is important to test each formula before adding them to the pick expression. Check in a text box, or a dimensionless straight table box.
I would also suggest starting off with a single expression in the source file, something simple like sum(amount), and test that. When that is working add a couple more expressions and test again. And so on.
Regards
Jonathan
Tnx!
It really works now
Currently I have one more problem: not all my metrics are displayed in the chart i finally got.
I lost 6 metrics. They are all have correct formulas, parseable in QlikView.
Some of them have very close to 0 amout (and I suspect this is the reason they aren`t displayed in the chart where most values - the millions), but the rest of metrics have normal amounts..
Formulas are properly, they all included in the result of concut () function and have uniq Sort_Order.. but don`t work in this solution
Here is formulas:
Sum({$<[Value Type]={10}, Metric = {'Client Service'}>}Amount) / (Sum({$<[Value Type]={10}, Metric = {'Total CS'}>}Amount)* Period_Days
Sum({$<[Value Type]={10}, Metric = {'Revenue'}>}Amount)/Sum({$<[Value Type]={10}, Metric = {'Client Service'}>}Amount)
(Sum({$<[Value Type]={10}, Metric = {'Total CS'}>}Amount)- Sum({$<[Value Type]={10}, Metric = {'Partner'}>}Amount))/Sum({$<[Value Type]={10}, Metric = {'Partner'}>}Amount)
Other formulas have the same structure and work properly, what`s going on with these I can`t imagine .
I don`t suppres obviously any values in the chart...
I have the same problem. Although all formulas are parseable in QlikView, some formulas are not shown in the straight table. If I change these formulas with much easier they still are not shown in the table.
Any ideas?
Forget it,
The problem was in my data model. Thank you all!