1 Reply Latest reply: Aug 17, 2017 1:07 PM by Felip Drechsler RSS

    Create Custom Table with each row as a different expression

    Abhay Sudhakaran

      Required output:

       

      MetricActual YTDTarget%ofTargetPrior Year%of Prior Year
      Open Sales (in $000s)1000N/AN/AN/AN/A
      Won Sales(in $000s)2500N/AN/AN/AN/A
      Average Rate (in $)100N/A
      Expenses (in $)1003000.33%2000.45%
      Hours Worked (in Hrs)5007500.66%8000.625%

       

       

      I need to produce a table which contains different metrics and each metric might present numbers in a different unit. Any help is appreciated.

        • Re: Create Custom Table with each row as a different expression
          Felip Drechsler

          Hi Abhay,

           

          Do the following:

          For dimension, use the expression:

           

          =ValueList('Open Sales (in $000s)','Won Sales(in $000s)','Average Rate (in $)','Expenses (in $)','Hours Worked (in Hrs)')

           

          And for each measure in 'Actual YTD', 'Target', '% Target', 'Prior Year' '%of Prior Year' use:

           

          pick

          (

          match

              (

              ValueList('Open Sales (in $000s)','Won Sales(in $000s)','Average Rate (in $)','Expenses (in $)','Hours Worked (in Hrs)'),

                  'Open Sales (in $000s)','Won Sales(in $000s)','Average Rate (in $)','Expenses (in $)','Hours Worked (in Hrs)'

              ),

              1, // put expression here

              2, // put expression here

              3, // put expression here

              4, // put expression here

              5  // put expression here

          )

           

          So when you change the expressions 1,2,3,4,5 for what you want, each measure will have a different expression, computing different things.

           

          Felipe.