12 Replies Latest reply: Mar 10, 2016 4:24 PM by PAUL YEO RSS

    How to combine 2 chart into 1 ?

    PAUL YEO

      Hi All

       

       

      I have chart 1 Expression as below :-

       

       

      money(

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

      , $(vMoneyFormatK))

       

       

       

       

      I have chart 2 expression as below :-

       

       

      money(pick(

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

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

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

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

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

      $(vMoneyFormatK))

       

       

      My imagine Solution as below not working  :-

       

       

      money(pick(

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

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

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

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

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

      match([Pls select 6st Dim],'sales','cost','profit'),

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

      $(vMoneyFormatK))

      )

       

       

      Hope some one can advise me where go wrong ?

       

      Next posting i will post my QC Doc.

       

       

      Paul

        • Re: How to combine 2 chart into 1 ?
          kushal chawda

          are [Pls select 66 Dim] & [Pls select 6st Dim] different field?

            • Re: How to combine 2 chart into 1 ?
              PAUL YEO

              Hi Sir

               

              They are diff field name. By the way i try using Mr Avinash expression , and insert my new condition , it work fine :-

               

              if

              (

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

              money(

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

              $(vMoneyFormatK)),

              if

              (

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

              money(

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

              $(vMoneyFormatK)),

              if

              (

              match([Pls select 6st Dim],'sales','cost','profit'),

              money(

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

              $(vMoneyFormatK)),

              )))

               

              But now i encounter , some issue , that is the chart take very long to plot. may i know it there any expression can make the chart plot faster ?

              • Re: How to combine 2 chart into 1 ?
                PAUL YEO

                Hi Kush

                 

                I just happen to join the 2 ColumnDim into 1, now my chart plot very fast. This new break thru will save me many sheet. Thank you very much,

                 

                Paul

                 

                Sent from my iPhone

              • Re: How to combine 2 chart into 1 ?
                kushal chawda

                try below

                 

                if(getselectedcount([Pls select 66 Dim])>0,

                money(pick(

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

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

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

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

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

                $(vMoneyFormatK)),

                if(getselectedcount([Pls select 6st Dim])>0,

                money(Pick(match([Pls select 6st Dim],'sales','cost','profit'),

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

                $(vMoneyFormatK))))

                 

                or

                 

                alt(

                money(pick(

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

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

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

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

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

                $(vMoneyFormatK)),

                 

                money(Pick(match([Pls select 6st Dim],'sales','cost','profit'),

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

                $(vMoneyFormatK)))

                  • Re: How to combine 2 chart into 1 ?
                    PAUL YEO

                    Hi Kush

                     

                     

                    Thank you for offer me 2 proposal on expression . I have try but it never work .

                     

                     

                    Can you advise me pls ?

                     

                     

                    Paul

                      • Re: How to combine 2 chart into 1 ?
                        kushal chawda

                        hav you tried this?

                         

                        alt(

                        money(pick(

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

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

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

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

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

                        $(vMoneyFormatK)),

                         

                        money(Pick(match([Pls select 6st Dim],'sales','cost','profit'),

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

                        $(vMoneyFormatK)))

                        • Re: How to combine 2 chart into 1 ?
                          Sunny Talwar

                          Slight modification to first expression proposed by Kush:

                           

                          If(GetSelectedCount([Pls select 66 Dim]) > 0,

                          Money(Pick(Match([Pls select 66 Dim],'rEVENUE','sales','eXP','n_PRO'),

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

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

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

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

                          If(GetSelectedCount([Pls select 6st Dim]) > 0,

                          Money(If(Match([Pls select 6st Dim],'sales','cost','profit'),

                          Sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}$(ColumnDim51)/$(Columndim89)/1000)), $(vMoneyFormatK))))

                          • Re: How to combine 2 chart into 1 ?
                            Sunny Talwar

                            Slight modification to the second expression:

                             

                            Alt(

                            Money(Pick(Match([Pls select 66 Dim],'rEVENUE','sales','eXP','n_PRO'),

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

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

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

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

                             

                            Money(If(Match([Pls select 6st Dim],'sales','cost','profit'),

                            Sum({$<year = {">=$(=max(year),4)"}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}$(ColumnDim51)/$(Columndim89)/1000)), $(vMoneyFormatK)))

                              • Re: How to combine 2 chart into 1 ?
                                PAUL YEO

                                Hi Sunny

                                 

                                Finally now working fine , as long as i never use the if , the chart display out very fast.

                                 

                                Also i must thank Kush , he already mentioned in his post below msg , which i just notice.

                                 

                                try with Pick() & match() to optimize the IF

                                 

                                Below is the expression working fine :-

                                money(pick(

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

                                Sum({<$(ColumnDim67)={$(ColumnDim67)}>}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({<$(ColumnDim67)={$(ColumnDim67)}>}Amount)/$(Columndim89)*-1/1000,

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

                                $(vMoneyFormatK))

                                 

                                Paul