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

How to make my table 2 display -53 amount ?

Hi All

My Table 1 have below expression , which will display -53 amount is correct :-

=SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},nET_PROFIT={nET_PROFIT}>}Amount*1)/$(Columndim89)/1000

My Table 2 is able to display all field except nET_PROFIT :-

money(pick(

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

SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},rEVENUE={rEVENUE}>}Amount*1)/$(Columndim89)/1000,

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

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

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

SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},eXP={eXP}>}Amount*1)/$(Columndim89)/1000,

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

$(vMoneyFormatK))

Can some one tell me how to make the table 2 display -53 when user click in nET_PROFIT ?

Paul

1 Solution

Accepted Solutions
paulyeo11
Master
Master
Author

Hi Kush

I manage to solve the issue , i add one more 'nET_PROFIT'  to below expression and it work fine. i don't know why ?

money(pick(

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

SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},rEVENUE={rEVENUE}>}Amount*1)/$(Columndim89)/1000,

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

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

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

SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},eXP={eXP}>}Amount*1)/$(Columndim89)/1000,

SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},nET_PROFIT={nET_PROFIT}>}Amount*1)/$(Columndim89)/1000,

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

$(vMoneyFormatK))

View solution in original post

8 Replies
Digvijay_Singh

One finding is - Your 'eXP' expression should come before nET_PROFIT, as per the order in match statement, not sure if that is making difference.

paulyeo11
Master
Master
Author

Hi Sir

I have try to copy the table 1 expression to last row , which is after eXP. I notice the whole expression return error.

And worst thing is I don't know why?

Paul

Sent from my iPhone

Kushal_Chawda

Hi,

Column66 is not available in your data model. Due to which expression is not evaluating. Please check

Digvijay_Singh

Sorry, its my bad, I took profit expression as net profit one. You have correct order but you are using variable in left side of set expression as -


{<$(ColumnDim66)={$(ColumnDim66)}


Left side must be a field. Try if you can correct that.

Digvijay_Singh

Ya, $ is misplaced it seems

paulyeo11
Master
Master
Author

Hi Kush

Table 1 Expression is working fine :-

=SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},nET_PROFIT={nET_PROFIT}>}Amount*1)/$(Columndim89)/1000

You are right the below expression Column66 is not in my data model. but it does not cause any issue to the overall expression. But when i replace above expression to below , the overall expression no more working.

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

Can you share with me where go wrong ?

Thank you

Paul

paulyeo11
Master
Master
Author

Hi Digvijay

Thank you for your sharing.

Mr Kush also spot column66 is not in my data model. but now my issue when i replace with the correct expression the whole expression not working.

Paul

paulyeo11
Master
Master
Author

Hi Kush

I manage to solve the issue , i add one more 'nET_PROFIT'  to below expression and it work fine. i don't know why ?

money(pick(

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

SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},rEVENUE={rEVENUE}>}Amount*1)/$(Columndim89)/1000,

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

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

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

SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},eXP={eXP}>}Amount*1)/$(Columndim89)/1000,

SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},nET_PROFIT={nET_PROFIT}>}Amount*1)/$(Columndim89)/1000,

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

$(vMoneyFormatK))