12 Replies Latest reply: Mar 10, 2016 4:24 PM by Yeo Poh sai

How to combine 2 chart into 1 ?

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 ?

My QV Doc

• Re: How to combine 2 chart into 1 ?

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

• Re: How to combine 2 chart into 1 ?

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 ?

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 ?

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 ?

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 ?

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 ?

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 ?

I think I missed on closing bracket. Thanks sunny for correction

• Re: How to combine 2 chart into 1 ?

And I think the second If(GetSeletedCount... statement either needs to repeat the expression 3 times (for sale, cost and profit), or use if instead of Pick

• Re: How to combine 2 chart into 1 ?

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 ?

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