Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Vince_CH
Creator II
Creator II

how to ignore the selection of one dimension in a multiple set analysis

Dear All,

I have an set analysis calculation like below in a table,  and i want to disregard the selection of the dimension of fiscal year in this case.  and i had tried in different ways like below but it didn't work, can you help??

Sum({<[Order Type]={'Y470','Y471'},DateType={'Order'},Order_Material_Pricing_Group_Text={'Lab machines', 'machines', 'Mills'}, [Material Number]-={'NSCMACHINE', 'NSCTANK'}>} Aggr(Order_Item_Quantity,[Order No.],[Item Number]))

 

Trial 1:

Sum({<[Order Type]={'Y470','Y471'},DateType={'Order'},FiscalYear=,Order_Material_Pricing_Group_Text={'Lab machines', 'machines', 'Mills'}, [Material Number]-={'NSCMACHINE', 'NSCTANK'}>} Aggr(Order_Item_Quantity,[Order No.],[Item Number]))

 

Trial 2:

Sum({<[Order Type]={'Y470','Y471'},DateType={'Order'},Order_Material_Pricing_Group_Text={'Lab machines', 'machines', 'Mills'}, [Material Number]-={'NSCMACHINE', 'NSCTANK'}>} <Total FiscalYear=>,Aggr(Order_Item_Quantity,[Order No.],[Item Number]))

Labels (1)
1 Solution

Accepted Solutions
jonathandienst
Partner
Partner

You need the set logic inside the Aggr() as well - something like:

Sum({<
		[Order Type]={'Y470','Y471'},
		DateType={'Order'},
		FiscalYear=,
		Order_Material_Pricing_Group_Text={'Lab machines', 'machines', 'Mills'}, 
		[Material Number]-={'NSCMACHINE', 'NSCTANK'}
	>} 
	
	Aggr(Only(
		{<
			[Order Type]={'Y470','Y471'},
			DateType={'Order'},
			FiscalYear=,
			Order_Material_Pricing_Group_Text={'Lab machines', 'machines', 'Mills'}, 
			[Material Number]-={'NSCMACHINE', 'NSCTANK'}
		>} 	
		Order_Item_Quantity)
	,[Order No.],[Item Number])
)
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

11 Replies
Arthur_Fong
Partner
Partner

Why do you need aggr after sum?

Expression should looks like below if you need aggr function:

Aggr(Sum({<[Order Type]={'Y470','Y471'},DateType={'Order'},FiscalYear=,Order_Material_Pricing_Group_Text={'Lab machines', 'machines', 'Mills'}, [Material Number]-={'NSCMACHINE', 'NSCTANK'}>} Order_Item_Quantity),[Order No.],[Item Number])

 

Vince_CH
Creator II
Creator II
Author

thanks for reply, Arthur.

the reason of putting aggr() after Sum is because, there are repeated lines in the original source file, so that we need to use aggr() firstly to define the unique line then sum. 

however the point here is how to disregard the selection of the dimension of FiscalYear in the set analysis, and i had tried in your way like my trial 1 indicated, but it didn't work. If I selected certain fiscal year, the table results still changes as per my selection, how to keep them fixed??

Arthur_Fong
Partner
Partner

You have a sample app?

Arthur_Fong
Partner
Partner

Can you try:

Sum({<[Order Type]={'Y470','Y471'},DateType={'Order'},FiscalYear=,Order_Material_Pricing_Group_Text={'Lab machines', 'machines', 'Mills'}, [Material Number]-={'NSCMACHINE', 'NSCTANK'}>} distinct Order_Item_Quantity)

Rodj
Luminary Alumni
Luminary Alumni

I think anything else associated with FISCAL_YEAR that may be filtered as a result also needs to be set the way you have with FISCAL_YEAR. There's likely other fields in your app that are affected.

Vince_CH
Creator II
Creator II
Author

@Arthur_Fong  thanks.

unfortunately i have no sample app so far. the order_item_quantity cannot be written in this way, because it was also linked with which order number it relates, so you has to keep my original structure like that: Sum(aggr(...)),

with "Fiscalyear= " added into the set analysis it doesn't work out. 

Vince_CH
Creator II
Creator II
Author

@Rodj  what do you exactly mean here? can you explain in details? and further how to resolve this? thanks

jonathandienst
Partner
Partner

You need the set logic inside the Aggr() as well - something like:

Sum({<
		[Order Type]={'Y470','Y471'},
		DateType={'Order'},
		FiscalYear=,
		Order_Material_Pricing_Group_Text={'Lab machines', 'machines', 'Mills'}, 
		[Material Number]-={'NSCMACHINE', 'NSCTANK'}
	>} 
	
	Aggr(Only(
		{<
			[Order Type]={'Y470','Y471'},
			DateType={'Order'},
			FiscalYear=,
			Order_Material_Pricing_Group_Text={'Lab machines', 'machines', 'Mills'}, 
			[Material Number]-={'NSCMACHINE', 'NSCTANK'}
		>} 	
		Order_Item_Quantity)
	,[Order No.],[Item Number])
)
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Stephen5
Contributor
Contributor

It is however very simple at heart. You can add a Set to an expression which changes the selection context for that expression – effectively forcing selections that only apply to that one expression. So sum(Value) becomes sum({<Type={‘Relevant’}>}Value), if you only want to add up the values which are flagged as being Relevant in the data myprepaidcenter