
Re: Using formulas from excel in the straight table
Fabrice Aunez Oct 3, 2013 9:19 AM (in response to Alena Tsyrfa)Hi,
If I understand your metrics are in a XLS file and the user can choose them through a ListBox.
Create many expressions in the chart that will be or nor visible:
a) expression : $(=subfield(concat(Formula, ''), '', n))
b) visible ? enable the conditional for each expression : getSelectedCount(Metric) >= n
(where n is your nth expression)
so, if you choose 3 expressions, the user will see the 3 chosen even though you have 20 in the chart.
Fabrice

Re: Using formulas from excel in the straight table
Alena Tsyrfa Oct 3, 2013 9:41 AM (in response to Fabrice Aunez)Thnx, Fabrice,
But, my situation is another.
1. user could`t choose metrics, i have straight table with constant list of metrics. I should display all at once metrics with values without any selections
2. i can`t create so many expressions as I have metrics  I have a lot of metris.
My main problem is:
i can`t display calculated according the loaded from the file formula value, only the text of this formula is displayed in the table.


Re: Using formulas from excel in the straight table
Fabrice Aunez Oct 3, 2013 9:53 AM (in response to Alena Tsyrfa)Ah ...
I will be interested by the solution ...
Metric as dimension and $(=Formula) as expression will not work
Fabrice

Re: Using formulas from excel in the straight table
Fabrice Aunez Oct 3, 2013 10:37 AM (in response to Alena Tsyrfa)I have perhaps something for you:
Dimension : Metric
Expression : pick(rowno(), $(=concat( Formula, ',')) )
You can add a parameter to the concat function : the set, the order of the measure (this parameter, I think, must be set to be equal to the order of Metric dimension in the table/chart.
QV will rewrite the function before executing it : you must have the ',' as delimiter but it can hurt some dataset you have written in you formulas !
Tell me if it works in real world (I have made just a test with few measures)
Fabrice

Re: Using formulas from excel in the straight table
Alena Tsyrfa Oct 4, 2013 6:24 AM (in response to Fabrice Aunez)Thx, Fabrice!
The solution you offered would work, if I didn`t have so many commas in the my metric formulas.
As you noticed it doesn`t work with commas in formulas

Re: Using formulas from excel in the straight table
Fabrice Aunez Oct 4, 2013 6:30 AM (in response to Alena Tsyrfa)yes, the solution works when there is no comma (I got it)
in fact the pick function expects commas (,). it is why we need to use concat with the delimiter comma also to get a true interpretation. And it hurts your formulas that contain a comma (in the sets). I tried with another delimiter without any success.
Perhaps change the equation of your formulas (instead of comma use a pipe like ) that you will substitute at the end. With this change, there will be no bad interaction with the pick function.
i think that the solution is very near.
Fabrice

Re: Using formulas from excel in the straight table
Jonathan Dienst Oct 8, 2013 3:33 AM (in response to Alena Tsyrfa)Hi
I have done something similar, although I created the pick() expression in a variable. This is the code I used:
MetricsConcat:
LOAD Concat(Expression, ',', LCR.LineNo) AS ReportExpressions
Resident LCR.ExpressionMap;
Let vMetrics = 'pick(LCR.ExprLine,' & peek('ReportExpressions') & ')';
Many of the expressions have commas, but it works correctly. In a table that uses LCR.ExprLine as a dimension, I use $(vMetrics) as the expression.
Regards
Jonathan

Re: Using formulas from excel in the straight table
Fabrice Aunez Oct 8, 2013 3:38 AM (in response to Jonathan Dienst )Jonathan,
Clever. I will test it. I was not able to solve the problem of commas ...
Fabrice

Re: Using formulas from excel in the straight table
Alena Tsyrfa Oct 8, 2013 5:35 AM (in response to Jonathan Dienst )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 (Sortorder, '& Peek ('FormulaList')&')'
but I can`t get result with $(vFormula) in the chart..

Re: Using formulas from excel in the straight table
Jonathan Dienst Oct 8, 2013 7:10 AM (in response to Alena Tsyrfa)Alena
Make sure that SortOrder is a dimension in the chart, or is uniquely associated (1:1) with a dimension in the chart. The expression needs a value of SortOrder.
You can access the individual formulas like this (eg in a text box, to show formula #107):
=Only({<SortOrder = {107}>} $(Formula))
I packaged this into a parameterized macro defined like this :
mExpr = Only({<SortOrder = {$1}>} $(Formula))
Then I can use it in a textbox like this:
$(mExpr(107))
Regards
Jonathan

Re: Using formulas from excel in the straight table
Jonathan Dienst Oct 8, 2013 7:22 AM (in response to Jonathan Dienst )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

Re: Using formulas from excel in the straight table
Alena Tsyrfa Oct 8, 2013 10:45 AM (in response to Jonathan Dienst )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...

Re: Using formulas from excel in the straight table
Greg Dimakis Oct 9, 2013 1:54 AM (in response to Alena Tsyrfa)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?

Re: Using formulas from excel in the straight table
Greg Dimakis Oct 9, 2013 2:37 AM (in response to Greg Dimakis)Forget it,
The problem was in my data model. Thank you all!









Re: Using formulas from excel in the straight table
Greg Dimakis Oct 8, 2013 2:58 AM (in response to Alena Tsyrfa)Hi,
I was following the discussion and the topic gave me the idea of the creation of a dynamic income statement. I tried to adopt a very simple example (ie with no commas on set analysis) but I couldn't get the desired results. If you see the attachment, the third line of the straight table shall give me the total sales. When applying on a list box, the value is coming properly (probably meaning that the pick function is ok). When in straight table I cannot get the results. I guess it is something in the set analysis.
I guess, if we get this solution the next step is to find a way to treat more complex formulas (ie having commas).
Any help will be appreciated.
Thanking you in advance.
Greg

FST.qvw 167.5 K

Re: Using formulas from excel in the straight table
sujeet singh Oct 8, 2013 5:36 AM (in response to Greg Dimakis)Greg,
Can you please explain the expression with peek you have used.

Re: Using formulas from excel in the straight table
Greg Dimakis Oct 8, 2013 6:53 AM (in response to sujeet singh)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

