Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using formulas from excel in the straight table


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:

MetricFormula
a

Sum({$<[Type] = {1},Metric = {'A'}>}Amount)

bSum({$<[Type] = {1},Metric = {'B'}>}Amount)
cSum({$<[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 NameVolume

A

100500
B809
C568

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?

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

17 Replies
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

Ah ...

I will be interested by the solution ...

Metric as dimension and $(=Formula) as expression will not work

Fabrice

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Jonathan,

Clever. I will test it. I was not able to solve the problem of commas ...

Fabrice