Skip to main content
Announcements
Defect acknowledgement with Nprinting Engine May 2022 SR2, please READ HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Create Custom Table with each row as a different expression

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.

1 Reply
felipedl
Partner - Specialist III
Partner - Specialist III

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.