8 Replies Latest reply: Mar 11, 2016 6:55 PM by Yeo Poh sai

# 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

• ###### Re: How to make my table 2 display -53 amount ?

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.

• ###### Re: How to make my table 2 display -53 amount ?

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

• ###### Re: How to make my table 2 display -53 amount ?

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.

• ###### Re: How to make my table 2 display -53 amount ?

Ya, \$ is misplaced it seems

• ###### Re: How to make my table 2 display -53 amount ?

Hi Digvijay

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

• ###### Re: How to make my table 2 display -53 amount ?

Hi,

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

• ###### Re: How to make my table 2 display -53 amount ?

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

• ###### Re: How to make my table 2 display -53 amount ?

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