Why pick(match expression i cannot remove 2nd last row of useless script ? Hi All I have below expression working fine FROM CHART 1:- money(pick( match([Pls select 601st Dim],'nET_PROFIT','sales','cost','profit','eXP','rEVENUE','rEVENUE'), Sum({<$(Colu

[paulyeo11]

Hi All

I have below expression working fine FROM CHART 1:-

money(pick(

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

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

sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}sales/$(Columndim89)/1000),

sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}cost/$(Columndim89)/1000),

sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}profit_amt/$(Columndim89)/1000),

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

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

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

$(vMoneyFormatK))

Since the rEVENUE is extra ( 2nd row of the above expression , and last 2nd row of the expression not require , i decide to remove it :-

first i remove 'rEVENUE' and it still working fine , see chart 2 :-

money(pick(

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

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

sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}sales/$(Columndim89)/1000),

sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}cost/$(Columndim89)/1000),

sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}profit_amt/$(Columndim89)/1000),

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

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

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

$(vMoneyFormatK))

secondly i remove 2nd last row of the expression, See chart 3 :-

money(pick(

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

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

sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}sales/$(Columndim89)/1000),

sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}cost/$(Columndim89)/1000),

sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}profit_amt/$(Columndim89)/1000),

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

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

$(vMoneyFormatK))

My question is how to make the above expression work ?

Paul

[paulyeo11]

Hi All

Sorry i accidentally paste the body text into the title text , and i cannot edit.

enclosed my sample QVW file .

Paul

[sunny_talwar · Accepted solution]

May be because a parenthesis is missing

Money(Pick(

Match([Pls select 601st Dim],'nET_PROFIT','sales','cost','profit','eXP','rEVENUE'),

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

Sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}sales/$(Columndim89)/1000),

Sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}cost/$(Columndim89)/1000),

Sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}profit_amt/$(Columndim89)/1000),

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

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

$(vMoneyFormatK))

[tamilarasu]

Hi Paul,

Seems you missed a bracket at the end of the expression. Try below expression

money(pick(

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

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

sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}sales/$(Columndim89)/1000),

sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}cost/$(Columndim89)/1000),

sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}profit_amt/$(Columndim89)/1000),

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

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

$(vMoneyFormatK))

[paulyeo11]

Hi sunny

thank , this is what i want to know.

Paul

[tamilarasu]

Could you close this thread by marking Sunny's answer as correct.