Expressions in Fields

[johnso2080]

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

[#2]

Hi,

Could you please share sample qvw file with some data?

Regards,

Neha

[jonathan_dau · Accepted solution]

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

[jonathandienst]

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

[#5]

hi,

Can you share an example?

Thanks,

Emílio

[#6]

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..

[jordidelucio]

Hi,

I have this exact same problem, I have tried your solution but must be doing something wrong or missing something as it does not work.

In my situation a MySQL DB and a separate unconnected XLS spreadsheet exist, no join or concatenate is possible as there is no common columns and no two columns called the same

I have a XLS table whose columns contain the ID and the Query. Query means the name of the column in the MySQL DB whose data for a particular time period must be fetched. If my user selections provide a single row, then it does show data. Else, it does not. When I added your solution, including portion of script it never shows anything in my new column vMetrics, regardless of one or more rows of the DB being selected. Do you have any qvw file available?