Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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

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

1 Solution

Accepted Solutions
sunny_talwar

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))

View solution in original post

5 Replies
paulyeo11
Master
Master
Author

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

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
Champion
Champion

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
Master
Master
Author

Hi sunny

thank , this is what i want to know.

Paul

tamilarasu
Champion
Champion

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