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

    Expressions in Fields

    Darren Johnson



      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






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





        • Re: Expressions in Fields
          Neha Rangari



          Could you please share sample qvw file with some data?




          • Re: Expressions in Fields
            Jonathan Dau

            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



            • Re: Expressions in Fields
              Jonathan Dienst



              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:


                   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


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