custom table - formulas in xls

[#1]

Hi Guys/gourous/newbie,

I do have a challenge (I know it's feasible, I did it long time ago but my brain is rusty... dammed !! I don't age well)

I need to build a table base on a really particular format.

The formatting and the formulas are both stored in an xls.

Xls in input (i'm not sure if the formulas have to start by '=' or not.. so I tried both)

My challenge is to be able to  interprète the formulas in my table in my table. I get them as a string or the interpretation  works when I select 1 single Row Label. It's disappearing when you get more than one.

I tried different formulas :

=FirstSortedValue(Formula,RowID)          -- give the formula as a string

$(=FirstSortedValue(Formula,RowID))      --interprete the formula for 1 single rowLabel selected

Please any help?

Thanks a lot ,

Cheers

[sushil353]

Hi,

Check this out: Store expression into excel sheet and re-use expressions in many charts using variables

HTH

[tresesco]

AFAIK, this is a known limitation. I am not sure if am being wrong saying this a 'limitation'. But yes, it doesn't work the way we want as you described above (Only works when you select to reduce to single row). As an alternate way, you can consider using variables as explained here: Set Analysis in Expression (Chart)?

[#4]

Hi Sushil,

Thanks for your answer as the number of rows will probably change, I tried to avoid to go though variables. (less maintenable).

@Amit, I'm sure I made it working 2 years ago for a client :S

[vinieme12]

This might give you some insight

Loading Expressions From File - WITHOUT creating variables in Script

[Peter_Cammaert · Accepted solution]

If you intend to create a table in which every row has to show a different expression, then you're indeed hitting an annoying limitation of QlikView. Almost everyone has tried this when in need of a Metrics table and failed in exactly the same way. However, there are very logical reasons why this doesn't work without a workaround:

• $-sign expressions are evaluated before a table is calculated. Therefor, the output cannot change from row to row.
• In the UI, you cannot evaluate the output of another expression, like you can in a load script with the eval() function.
• ...

However, Ralf Becher once demonstrated a great technique that can be used as a workaround. It's based on the nested IF-THEN-ELSE trick. You will have to adapt it so that a single variable is created during a reload that checks every dimension value from your Excel and produces the corresponding formula for evaluation in the THEN parameter.

See here.  Re: Re: Chart with count of $Field values‌

Because Metrics tables do not have large numbers of rows (usually), a giant IF-THEN-ELSE is ok here.

Best,

Peter