Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
An issue has been identified on Qlik Cloud hub, please visit our Status Update Page for details: GET THE LATEST
cancel
Showing results for 
Search instead for 
Did you mean: 
vvira1316
Specialist II
Specialist II

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

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

Review the bar charts seen in screen shot below.

GDT and ADT Chart wo Selection.PNG

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.

GDT and ADT Chart w Selection in GDT.PNG

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.

GDT and ADT Chart w Selection in GDT2.PNG

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

GDT and ADT Chart w Selection in ADT.PNG

instead it should look like below...

GDT and ADT Chart w Selection in ADT2.PNG

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

Regards,

Vijay

1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

13 Replies
sunny_talwar

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
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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

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