Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

paulyeo11
Not applicable

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
Not applicable

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

8 Replies
Digvijay_Singh
Not applicable

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.

paulyeo11
Not applicable

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

kush141087
Not applicable

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

Digvijay_Singh
Not applicable

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.

Digvijay_Singh
Not applicable

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

Ya, $ is misplaced it seems

paulyeo11
Not applicable

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

paulyeo11
Not applicable

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

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
Not applicable

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