topic Re: How to sync behavior of two bar charts (Need help) in QlikView

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

vvira1316 — Tue, 06 Jun 2017 18:56:04 GMT

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

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

sunny_talwar — Tue, 06 Jun 2017 19:04:21 GMT

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)

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

vvira1316 — Tue, 06 Jun 2017 19:20:00 GMT

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

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

sunny_talwar — Tue, 06 Jun 2017 19:23:15 GMT

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

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

vvira1316 — Tue, 06 Jun 2017 19:36:25 GMT

Hi Sunny,

[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?

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

sunny_talwar — Tue, 06 Jun 2017 19:39:23 GMT

multiply your expression with this

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

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

vvira1316 — Tue, 06 Jun 2017 19:52:13 GMT

Hi Sunny,

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

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

BR,

Vijay

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

sunny_talwar — Tue, 06 Jun 2017 19:53:29 GMT

Look at my response here:

Re: Range Sum

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

vvira1316 — Wed, 07 Jun 2017 22:07:23 GMT

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....

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

sunny_talwar — Thu, 08 Jun 2017 09:08:44 GMT

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

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

vvira1316 — Thu, 08 Jun 2017 15:08:16 GMT

Not in this situation. If I select any values in second chart then KPI # is not getting calculated properly. If I select any values in Global Document Type then KPI# and chart behavior are fine... as can be seen in screenshots below....

ADT Selection ---- First KPI calculation not working......

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

sunny_talwar — Thu, 08 Jun 2017 15:13:25 GMT

Give this a shot

=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'}*p([Agreement Document Type]),
[Agreement Document Type]=
>}[Counterparty Document: Document ID])
,'###,###,###')

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

vvira1316 — Thu, 08 Jun 2017 15:25:16 GMT

Perfect..... thanks....

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

sunny_talwar — Thu, 08 Jun 2017 15:27:06 GMT

Great, remember to mark any helpful responses which you think might help future visitorsQlik Community Tip: Marking Replies as Correct or Helpful