3 Replies Latest reply: Mar 11, 2016 7:10 PM by Yeo Poh sai

# How to modify my expression from sum to Pick ( Match ?

Hi All

Chart 1 expression okay for sales ( when click on step 1 ) :-

money(pick(

match([Pls select 67 Dim],'rEVENUE','sales','cost','profit','eXP','n_PRO'),

Sum({<\$(ColumnDim67)={\$(ColumnDim67)}>}Amount)/\$(Columndim89)/1000,

Sum({<Year={'>\$(=year(today())-2)'}>} sales/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} cost/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} profit_amt/\$(Columndim89)/1000),

Sum({<\$(ColumnDim67)={\$(ColumnDim67)}>}Amount)/\$(Columndim89)*-1/1000,

Sum({<\$(ColumnDim67)={\$(ColumnDim67)}>}Amount)/\$(Columndim89)/1000),

\$(vMoneyFormatK))

Chart 3 expression as below , work fine too :-

Sum( {<Year={'>\$(=year(today())-2)'}, nET_PROFIT={'nET_PROFIT'}>}Amount )

When i press Step 2 , how to make Chart 4 look like chart 3 ?

My imagine expression for chart 4 as below :-

money(pick(

match([Pls select 67 Dim],'rEVENUE','sales','cost','profit','eXP','n_PRO'),

Sum({<\$(ColumnDim67)={\$(ColumnDim67)}>}Amount)/\$(Columndim89)/1000,

Sum({<Year={'>\$(=year(today())-2)'}>} sales/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} cost/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} profit_amt/\$(Columndim89)/1000),

Sum({<\$(ColumnDim67)={\$(ColumnDim67)}>}Amount)/\$(Columndim89)*-1/1000,

Sum( {<Year={'>\$(=year(today())-2)'}, nET_PROFIT={'nET_PROFIT'}>}Amount/1000),

\$(vMoneyFormatK))

But it never work , hope some one can advise me.

Paul

• ###### Re: How to modify my expression from sum to Pick ( Match ?

If this is a duplicate of your other thread, please close it. And also consider closing the other thread if your issue is resolved.

Thanks,
Stefan

• ###### Re: How to modify my expression from sum to Pick ( Match ?

Hi Swuehl

I just wake up , now my mine is fresh , i think i should be able to solve the problem and try to closed this thread as soon as possible,

Paul

• ###### Re: How to modify my expression from sum to Pick ( Match ?

Hi Swuehl

finally my expression work now , it is due to i need to add the 'nET_PROFIT' .

money(pick(

match([Pls select 67 Dim],'rEVENUE','sales','cost','profit','eXP','nET_PROFIT','nET_PROFIT'),

Sum( {<Year={'>\$(=year(today())-2)'}, rEVENUE={'rEVENUE'}>}Amount/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} sales/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} cost/\$(Columndim89)/1000),

Sum({<Year={'>\$(=year(today())-2)'}>} profit_amt/\$(Columndim89)/1000),

Sum( {<Year={'>\$(=year(today())-2)'}, eXP={'eXP'}>}Amount*-1/1000),

Sum( {<Year={'>\$(=year(today())-2)'}, nET_PROFIT={'nET_PROFIT'}>}Amount/1000),

Sum({<\$(ColumnDim67)={\$(ColumnDim67)}>}Amount)/\$(Columndim89)/1000),

\$(vMoneyFormatK))

By the way thank you very much for your help in the pass on the P&L script, which i remember you forever.

Paul