How to make my table 2 display -53 amount ?

[paulyeo11]

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

[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]

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]

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]

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 · Accepted solution]

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