Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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.
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
Hi,
Column66 is not available in your data model. Due to which expression is not evaluating. Please check
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.
Ya, $ is misplaced it seems
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
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
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))