topic Re: How to combine 2 chart into 1 ? in QlikView

How to combine 2 chart into 1 ?

paulyeo11 — Thu, 10 Mar 2016 09:20:41 GMT

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

paulyeo11 — Thu, 10 Mar 2016 09:21:37 GMT

My QV Doc

Re: How to combine 2 chart into 1 ?

Kushal_Chawda — Thu, 10 Mar 2016 09:31:40 GMT

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

Re: How to combine 2 chart into 1 ?

paulyeo11 — Thu, 10 Mar 2016 09:38:48 GMT

Hi Sir

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

(

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

money(

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

$(vMoneyFormatK)),

(

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

money(

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

$(vMoneyFormatK)),

(

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 ?

Kushal_Chawda — Thu, 10 Mar 2016 09:51:28 GMT

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

alt(

money(pick(

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

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 ?

paulyeo11 — Thu, 10 Mar 2016 10:34:30 GMT

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 — Thu, 10 Mar 2016 10:40:02 GMT

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)*-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 — Thu, 10 Mar 2016 10:46:40 GMT

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)*-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 — Thu, 10 Mar 2016 10:48:43 GMT

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

Kushal_Chawda — Thu, 10 Mar 2016 10:51:04 GMT

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

Re: How to combine 2 chart into 1 ?

sunny_talwar — Thu, 10 Mar 2016 10:53:33 GMT

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 ?

paulyeo11 — Thu, 10 Mar 2016 20:48:28 GMT

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

Re: How to combine 2 chart into 1 ?

paulyeo11 — Thu, 10 Mar 2016 21:24:16 GMT

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