topic Re: Dimension as value in a set analysis expression in App Development

Dimension as value in a set analysis expression

chethim982 — Wed, 13 Feb 2019 15:44:17 GMT

Hi,

I am trying to pass the dimension values of qlik table to a set analysis expression in the same table.

This needs to be accomplished in set analysis .

Data
Org_code	Org_name	Sales	Org_secondary_Code
1106	XYZ	106	1106
1102	XCV	30	1106
1106	UTY	40	1106
1106	IOP	40	1104
1101	HGF	120	1102
1102	GYH	330	1105
1104	FGH	130	1104



Requirement:	If Org_code and org_secondary_code are equal then report the sales against the Org_code in qlik table.
	we need to sync the Org_code and Org_secondary_code in set analysis expression. Which is not working.
	Org_code is an dimension and we need to pass the values of Org_code foreach row in the set expression.
Expected ouput:	Org_code	Org_name	Sales
	1106	XYZ	106+40
	1102	XCV	0
	1101	HGF	0
	1104	FGH	130

Re: Dimension as value in a set analysis expression

sunny_talwar — Wed, 13 Feb 2019 16:36:47 GMT

Why don't you create a flag in the script for this

If(Org_code = Org_secondary_Code, 1, 0) as Flag

and then use this in your expression

Sum({<Flag = {1}>}Sales)

Re: Dimension as value in a set analysis expression

dwforest — Wed, 13 Feb 2019 20:59:09 GMT

Set analysis is calculated once per chart (or table). NOT once per row so it is not the appropriate solution in your case.
You could use an If in script as Sunny suggests or in your table as Sales dimension:

If(Org_code = Org_secondary_Code, Sales, 0)

Not sure if having different names for Org_code 1106 in your example is intentional; if so you'd get unique lines for 1106 XYZ and 1106 UTY

Re: Dimension as value in a set analysis expression

chethim982 — Tue, 19 Feb 2019 12:41:43 GMT

Thanks!. It worked well with the flag. I made some changes to script as secondary Org code was coming from another table. so I merged the secondary code to the table where primary org code table with the below script.

Load*, If(primaryOrg_code=secondary_org_code,1,0) as flag

resident FACT_Primary;

drop table FACT_primary;

In the chart expression i just used the flag={1} in the set analysis expression Then everything worked well.