Comparing Associated with Non-Associ​ated Values

[#1]

Hello,

We have a business requirement, where we need to compare associated values with non-associated values. Can you please help me to get this done in qlikview. Attached Test.qvw with the sample data. Please note that, the actual data is having 1 Million Rows in table.

Here is the Usecase:

1. In Attached file data represents Various companies and their employee count from different countries. This data has been shown as pivot table as company as primary dimension, country as second dimension and Sum of Employees expression as Employee count.

2. Presently, If we select any country, because of the data associativity pivot table data will be shown all the companies associated with the selected country and only the selected country and its employee count.

  But our requirement is when we select any country, all the companies which are having branches in this country has to be display as primary dimension. And Secondary dimension should display the selected country along with that the companies which are having highest employee count(Non Associated) for that particular company and country.

  Let say if we select India as country, then below output is expecting from the attached qvw. 

Company	Country	Employee Count
Facebook	Africa	6500
	SAmerica	6500
	Canada	5500
	India	500
Google	Europe	10000
	Samerica	8500
	Africa	7500
	India	2000
Infosys	Samerica	4500
	Japan	4000
	Africa	3500
	India	15000
Microsoft	US	5000
	China	1500
	India	1000

[sushil353]

Hi,

This can be easily done by using total qualifier in your expression.

for example: count(Total <Country> Employee)

HTH

Sushil

[tresesco]

Play with p()/e(). Though I could not really get your point, but

May be like: =Sum({<Company=p(Company),Country=p({1<Company=p(Company)>}Country)>}Employees)

PFA

[ashfaq_haseeb]

Like this.

Regards

ASHFAQ

[#5]

Hi,

Thanks for the reply..

In this case, I need to get top 3 countries along with selected country in dimension. Total and All will be working when we need to aggregate the values for particular dimension... I have tried that option, but its not working...

Thanks,

Ram

[#6]

Thanks for the reply. I am looking for Similar output . But I just need to get Top 3 Countries having highest employee count other than the selected country.

[#7]

Thanks for the reply. I am looking for Similar output . But I just need to get Top 3 Countries having highest employee count other than the selected country.