2 Replies Latest reply: Mar 11, 2016 10:43 AM by PAUL YEO RSS

    Convert if expression to pick(match For Table

    PAUL YEO

      Hi All

       

       

       

       

      Table 1 expression work fine.

       

       

       

       

      if

      (

      match([Pls select 66 Dim],'rEVENUE'),

      money(

       

       

      SUM({<year = {"$(=Max(year)-0)"},month={"<=$(=month)"},$(ColumnDim51)={$(ColumnDim51)}>}Amount*1)/$(Columndim89)/1000,

      $(vMoneyFormatK)),

       

       

      if

      (

      match([Pls select 66 Dim],'sales'),

      money(

       

       

      sum({$<year = {$(=max(year)-0)}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}$(ColumnDim51)/$(Columndim89)/1000),

      $(vMoneyFormatK)),

       

       

      if

      (

      match([Pls select 66 Dim],'eXP'),

      money(

       

       

      SUM({<year = {"$(=Max(year)-0)"},month={"<=$(=month)"},$(ColumnDim51)={$(ColumnDim51)}>}Amount*1)/$(Columndim89)/1000,

      $(vMoneyFormatK)),

       

       

      if

      (

      match([Pls select 66 Dim],'n_PRO'),

      money(

       

       

      SUM({<year = {"$(=Max(year)-0)"},month={"<=$(=month)"},$(ColumnDim51)={$(ColumnDim51)}>}Amount*1)/$(Columndim89)/1000,

      $(vMoneyFormatK)),

       

       

       

       

      ))))

       

       

       

       

      I try to convert to below format which is using pick match :-

       

       

      money(pick(

      match([Pls select 67 Dim],'rEVENUE','sales','cost','profit','eXP','n_PRO'),

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

      Sum({<Year={'>$(=year(today())-2)'}>} sales/$(Columndim89)/1000),

      Sum({<Year={'>$(=year(today())-2)'}>} cost/$(Columndim89)/1000),

      Sum({<Year={'>$(=year(today())-2)'}>} profit_amt/$(Columndim89)/1000),

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

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

      $(vMoneyFormatK))

       

       

       

       

      My imagine expression as below :-

       

       

      money(pick(

      match([Pls select 66 Dim],'rEVENUE','sales','cost','profit','eXP','n_PRO'),

      SUM({<year = {"$(=Max(year)-0)"},month={"<=$(=month)"},$(ColumnDim51)={$(ColumnDim51)}>}Amount*1)/$(Columndim89)/1000,

      Sum({<Year={'>$(=year(today())-2)'}>} sales/$(Columndim89)/1000),

      Sum({<Year={'>$(=year(today())-2)'}>} cost/$(Columndim89)/1000),

      Sum({<Year={'>$(=year(today())-2)'}>} profit_amt/$(Columndim89)/1000),

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

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

      $(vMoneyFormatK))

       

       

      But cannot work. Hope some one can advise me.

       

       

      Paul

        • Re: Convert if expression to pick(match For Table
          Stefan Wühl

          Just from looking at your expression, I think there is a missing closing bracket:

           

          money(

          pick(

          match([Pls select 66 Dim],'rEVENUE','sales','cost','profit','eXP','n_PRO'),

          SUM({<year = {"$(=Max(year)-0)"},month={"<=$(=month)"},$(ColumnDim51)={$(ColumnDim51)}>}Amount*1)/$(Columndim89)/1000,

          Sum({<Year={'>$(=year(today())-2)'}>} sales/$(Columndim89)/1000),

          Sum({<Year={'>$(=year(today())-2)'}>} cost/$(Columndim89)/1000),

          Sum({<Year={'>$(=year(today())-2)'}>} profit_amt/$(Columndim89)/1000),

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

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

          )

          ,$(vMoneyFormatK) )

            • Re: Convert if expression to pick(match For Table
              PAUL YEO

              Hi Swuehl

               

              Thank you for your help , i notice that error is not missing bracket , i was some error in high lighted text :-

               

              money(pick(

              match([Pls select 67 Dim],'rEVENUE','sales','cost','profit','eXP','n_PRO'),

              SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},rEVENUE={rEVENUE}>}Amount*1)/$(Columndim89)/1000,

              Sum({<Year={'>$(=year(today())-2)'}>} sales/$(Columndim89)/1000),

              Sum({<Year={'>$(=year(today())-2)'}>} cost/$(Columndim89)/1000),

              Sum({<Year={'>$(=year(today())-2)'}>} profit_amt/$(Columndim89)/1000),

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

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

              $(vMoneyFormatK))

               

              Now it work fine.

               

              Paul