topic Re: Comparison between not associate data in QlikView

Comparison between not associate data

paulwalker — Thu, 11 Feb 2021 13:18:55 GMT

Hi Community,

I have one scenario, I have to compare data between non-associated data.

DataSet:

CODE	CATEGORY	Industry	InsCode	Salary
AAA	Qlik Sense	Banking	FIN.01.005.E4A	249996
AAA	Qlik Sense	Banking	FIN.01.005.E4A	5000
AAA	QlikView	Banking	AK.02.001.P40	38950
ABC	QlikView	Banking	AK.02.001.P40	39975
ABC	QlikView	Banking	AK.02.001.P40	4000
ABC	QlikView	Banking	AK.02.001.P40	39975
BBB	SWIFT	Banking	AK.02.001.P40	38000
XYZ	Spot Fire	Banking	FIN.02.001.P60	56375
XYZ	Spot Fire	Banking	FIN.02.001.P60	1350
123	Splunk	Banking	FIN.03.001.E30	1200
311	BO	Banking	FIN.03.001.E20	4590
AAA	Qlik Sense	Banking	FIN.03.001.E13	148596
1056	Tableau	Banking	TIN.03.002.E10	224052.5
ABC	QlikView	Banking	FIN.03.001.M50	56373
ABC	QlikView	Banking	FIN.03.001.M50	780
ABC	QlikView	Banking	FIN.03.001.M50	56375
1056	Tableau	Banking	FIN.03.002.M5	82808
1056	Tableau	Banking	FIN.03.002.M5	100000
1056	Tableau	Banking	FIN.03.002.M5	113861
ABC	QlikView	Banking	FIN.03.002.M5	56375
POP	Micro Strategy	Sales	AK.02.001.P40	56375
POP	Micro Strategy	Sales	AK.02.001.P40	38950
POP	Micro Strategy	Sales	FIN.03.001.E13	39975
POP	Micro Strategy	Sales	FIN.02.001.P90	38000
MAN	Power BI	Sales	FIN.02.001.P60	56375
MAN	Power BI	Sales	FIN.03.001.E30	82808
MEN	Birt	Sales	FIN.03.001.E20	107000
MEN	Birt	Sales	FIN.03.001.E13	113861
NIN	Agile	Sales	TIN.03.002.E10	56375
NIN	Agile	Sales	FIN.03.001.M50	38000
NIN	Agile	Sales	FIN.03.001.M50	56375

I did group by Salary based on Industry and InsCode and applied back to main table using mapping table

Row Labels	Average of Salary
Banking-AK.02.001.P40	32180
Banking-FIN.01.005.E4A	127498
Banking-FIN.02.001.P60	28862.5
Banking-FIN.03.001.E13	148596
Banking-FIN.03.001.E20	4590
Banking-FIN.03.001.E30	1200
Banking-FIN.03.001.M50	37842.66667
Banking-FIN.03.002.M5	88261
Banking-TIN.03.002.E10	224052.5
Sales-AK.02.001.P40	47662.5
Sales-FIN.02.001.P60	56375
Sales-FIN.02.001.P90	38000
Sales-FIN.03.001.E13	76918
Sales-FIN.03.001.E20	107000
Sales-FIN.03.001.E30	82808
Sales-FIN.03.001.M50	47187.5
Sales-TIN.03.002.E10	56375
Grand Total	64604.04839

Please find the attached spread sheet, I did exercise.

confusing with explanation, so attached spread sheet - hope it helps!

Thanks in Advance!

Re: Comparison between not associate data

edwin — Thu, 11 Feb 2021 22:05:26 GMT

you may not want to determine the average in the script. because when the user filters on code for example, then the average is wrong as the average disregards the code. you can easily determine the average in the UI instead:
avg(salary) and you can get the comparison using Salary against the average (with Salary as a dimension)

Re: Comparison between not associate data

paulwalker — Fri, 12 Feb 2021 08:17:24 GMT

Thanks for response!

Your right - average calculation is normal, but whatever user selected first set of selection - what we have data displayed and aggregated data, this data should be apply to second set of selections.

Created simple data set and attached spread sheet in detail..

I think hope it help below screen and attachment

Re: Comparison between not associate data

paulwalker — Fri, 12 Feb 2021 18:12:07 GMT

Anyone did face such type of requirement!

Please I need suggestions.

Re: Comparison between not associate data

edwin — Fri, 12 Feb 2021 19:53:44 GMT

try this:

Re: Comparison between not associate data

edwin — Fri, 12 Feb 2021 19:54:43 GMT

expression you are looking for is
=avg(total <InsCode> Salary)

InsCode needs to be in the chart so it can be properly aggregated

Re: Comparison between not associate data

edwin — Fri, 12 Feb 2021 20:07:42 GMT

im replying in reverse fashion

the understanding here is you want the average per InsCode regardless of Category and Industry and you want to compare per category and industry

also InsCode can be shared across Category and Industry looks like

Re: Comparison between not associate data

paulwalker — Sun, 14 Feb 2021 06:56:09 GMT

Anyone can help me on this?

I'm expecting result as attached screen.

Re: Comparison between not associate data

paulwalker — Sun, 14 Feb 2021 06:57:36 GMT

It's not working the way I expect it to

Re: Comparison between not associate data

paulwalker — Mon, 15 Feb 2021 05:29:47 GMT

@sunny_talwar, @MayilVahanan

I tried many ways, but could not achieve.

can you please have a look ?

Re: Comparison between not associate data

edwin — Thu, 18 Feb 2021 01:19:48 GMT

can you tell me what is not working? what your expectation is that isnt met? as you can see it is working per your declared requirements. maybe if you describe the user experience step by step it will be clear not just for me also for others who may be able to help