5 Replies Latest reply: Mar 31, 2016 10:05 PM by Tamil Nagaraj RSS

    Why pick(match expression i cannot remove 2nd last row of useless script ?  Hi All  I have below expression working fine FROM CHART 1:-  money(pick( match([Pls select 601st Dim],'nET_PROFIT','sales','cost','profit','eXP','rEVENUE','rEVENUE'), Sum({<$(Colu

    PAUL YEO

      Hi All

       

       

      I have below expression working fine FROM CHART 1:-

       

       

      money(pick(

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

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

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

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

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

      Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount)/$(Columndim89)*-1/1000,

      Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount)/$(Columndim89)*1/1000,

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

      $(vMoneyFormatK))

       

       

      Since the rEVENUE is extra ( 2nd row of the above expression , and last 2nd row of the expression not require , i decide to remove it :-

       

       

      first i remove 'rEVENUE' and it still working fine , see chart 2 :-

      money(pick(

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

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

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

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

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

      Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount)/$(Columndim89)*-1/1000,

      Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount)/$(Columndim89)*1/1000,

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

      $(vMoneyFormatK))

       

       

      secondly i remove 2nd last row of the expression, See chart 3 :-

      money(pick(

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

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

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

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

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

      Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount)/$(Columndim89)*-1/1000,

      Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount)/$(Columndim89)*1/1000,

      $(vMoneyFormatK))

       

       

      My question is how to make the above expression work ?

       

       

      Paul