How to sync behavior of two bar charts (Need help)

[vvira1316]

I've a situation where certain behavior of two bar charts has to be achieved.

Review the bar charts seen in screen shot below.

The Formula for left side (Global Document Type) chart is

=Count({<
[Document Status]={'Execution Docs Complete*'},
[Global Document Type]={'ISDA','MRA','MSFTA','GMRA','MSLA','NAESB','Foreign Exchange Master Agreement','Margin Lending Agreement','Prime Brokerage Agreement','Term Commitment Annex','Futures and Cleared Swaps Agreement','ISDA CREDIT SUPPORT ANNEX'}
>}[Counterparty Document: Document ID])

The Formula for right side (Agreement Document Type) chart is

=Count({<
[Version]={'>0'},
[Agreement Terms: Document Status]={'Execution Docs Complete*','Completed in Markit*'}
>}
[Agreement Terms: SFDC Credit Terms New number])

Now if user selects certain values in Global Document Type then Left side chart should display bars for those selected Global Document Type. Also Agreement Document Type chart on right should also display bars for selected Document Types. Currently the right side bar does display only selected document types but I'm not able to get it working for left side chart.

In above screenshot following Global Document Types have been selected.

Foreign Exchange Master Agreement,ISDA,ISDA CREDIT SUPPORT ANNEX,MRA,Futures and Cleared Swaps Agreement

I'm getting above values using =GetFieldSelections([Global Document Type], ',', 100)

I need following behavior to happen in above scenario instead.

Another situation: if user selects certain values in Agreement Document Type then Left side chart should display bars for those selected Agreement Document Type but with Initial Count and not a reduced count as can be seen in screen shot below

instead it should look like below...

Any help/guidance will be great.....

Regards,

Vijay

[sunny_talwar · Accepted solution]

Try this

=Count({<
[Document Status]={'Execution Docs Complete*'},
[Global Document Type]*={'ISDA','MRA','MSFTA','GMRA','MSLA','NAESB','Foreign Exchange Master Agreement','Margin Lending Agreement','Prime Brokerage Agreement','Term Commitment Annex','Futures and Cleared Swaps Agreement','ISDA CREDIT SUPPORT ANNEX'},

[Agreement Document Type] =
>}[Counterparty Document: Document ID])

* Avg({<[Global Document Type] = p([Agreement Document Type])>}1)

[vvira1316]

Hi Sunny,

Thanks for quick response. I'm not clear on what you are suggesting. My apology for not understanding it. Can you please provide your inputs in little more detail?

Avg({<[Global Document Type] = p([Agreement Document Type])>}1) is that for second situation?

Please provide more details so I understand better.

Regards,

Vijay

[sunny_talwar]

Yes, the multiplication with the Avg() expression is to take care of the second problem you were facing. For 1st issue, I just added an * so that selection in the field [Global Document Type] are based on intersection of selection and the set of values provided. More on this here:Implicit Set Operators

[vvira1316]

Hi Sunny,

=Count({<
[Document Status]={'Execution Docs Complete*'},
[Global Document Type]*={'ISDA','MRA','MSFTA','GMRA','MSLA','NAESB','Foreign Exchange Master Agreement','Margin Lending Agreement','Prime Brokerage Agreement','Term Commitment Annex','Futures and Cleared Swaps Agreement','ISDA CREDIT SUPPORT ANNEX'},

[Agreement Document Type] =
>}[Counterparty Document: Document ID])

it solved first behavior.... thanks a lot..... ( I was telling my colleague that I always like your responses and you are quick.... I was hoping and expecting you will see my question)

for second behavior what changes should I make?

[sunny_talwar]

multiply your expression with this

* Avg({<[Global Document Type] = p([Agreement Document Type]), [Document Status]={'Execution Docs Complete*'}>}1)

[vvira1316]

Hi Sunny,

It did solve both issues... thanks a lot.....

Can you please explain what multiplication for second part is doing?

BR,

Vijay

[sunny_talwar]

Look at my response here:

Re: Range Sum

[vvira1316]

Hi Sunny,

the chart behavior got resolved but one KPI number is not getting updated when values in second chart is selected...

I'm using the same formula as in chart for # in Text box

=Num(Count({<
[Document Status]={'Execution Docs Complete*'},
[Global Document Type]*={'ISDA','MRA', 'GMSLA', 'Exchange Agreement', 'Selling Guide', 'Priority Credit Line Agreement', 'General Account Agreement', 'MSFTA','GMRA','MSLA','NAESB','Foreign Exchange Master Agreement','Margin Lending Agreement','Prime Brokerage Agreement','Term Commitment Annex','WFS Futures and Cleared Swaps Agreement','ISDA CREDIT SUPPORT ANNEX'},
[Agreement Document Type]=
>}[Counterparty Document: Document ID])
* Avg({<[Global Document Type] = p([Agreement Document Type]), [Document Status]={'Execution Docs Complete*'}>}1)
,'###,###,###')

Any guidance....

[sunny_talwar]

And you are selecting in [Global Document Type] field?