5 Replies Latest reply: Apr 28, 2015 8:05 AM by MANISH SHARMA

# Expressions in Fields

Hello,

I have an excel spreadsheet which contains all my common expressions e.g "SUM( {\$<ThisYear={1}>} [Order Value])"

Along with the expression text it contains an ID for the expression, and it's name, and a couple of other attributes.

I want to create a PivotTable (or Straight Table) that shows the evaluated value of each expression, split by the dimensions that relate to that expression (name etc..) So i'd have...

This Year          Last Year

Orders

Revenue

Customers

... where each value is it's own evaluated expression.

I've tried a lot of different things but none seem to work, is it possible?

Any help would be hugely appreciated!

Thanks

Darren

• ###### Re: Expressions in Fields

Hi,

Could you please share sample qvw file with some data?

Regards,

Neha

• ###### Re: Expressions in Fields

Hi Darren,

I'm not sure it's the solution to your problem but if the expression is This Year in your chart you can make it with a If condition. For instance

This Year =

if( KPI Dimension = 'Orders', SUM( {\$<ThisYear={1}>} [Order Value]),

if( KPI Dimension = 'Revenue', SUM( {\$<ThisYear={1}>} [Revenue]),

....

If you have all your expressions in a spread sheet you can probably do

if( KPI Dimension = 'Orders', Only({<ID={ID of your expression Orders}>} Expression,

if( KPI Dimension = 'Revenue', Only({<ID={ID of your expression Revenue}>} Expression,

....

Hope that will help

Jonathan

• ###### Re: Expressions in Fields

Hi

The problem is that \$ expansions are performed before the chart data is processed and outside the context of the dimensions of the chart, so something like \$(=Only(Expression)) does not work as no unique value of Expression exists at the time of expansion. If you select one row in the table, the expression evaluates correctly.

To get around this, you need to assemble your expressions into a Pick(ID, expr, expr, expr, ....) statement. You can do this with this script fragment:

```   T_ConcatExpressions:
LOAD Concat(Expression, ',', ID) As ConcatExpression
Resident Expressions;

Let vMetrics = 'Pick(ID,' & Peek('ConcatExpression') & ')';
DROP Table T_ConcatExpressions;

```

Now you can create a straight table at the front end like this:

Dimension:  ID

Column 1:    Name

Column 2:    \$(vMetrics)

You can hide the dimension column, or even use Name as the dimension as long as each expression has a unique name.

Note the Concat statement needs the ID to ensure that the concat sorts the expressions by ID.

Do not be conerned about the size of the vMetrics expression. I have done this with concats that produce hundreds of lines in vMetrics. However, depending on the size of the data set and the number of expressions, evaluating this statement can becomes processor intensive. I get good performance with 50+ expressions on a 300,000 rows data set using a dual core PC.

Hope that helps

Jonathan

• ###### Re: Expressions in Fields

hi,

Can you share an example?

Thanks,

Emílio

• ###### Re: Expressions in Fields

I am working with sum( {\$<Year = {\$(=Only(Year))}>} Order_value) .. its working fine for current select year .. but for prev year m using sum( {\$<Year = {\$(=Only(Year)-1)}>} Order_value) , but its not giving me required .. Can any oen..