set analysis aggr sum with total by order

[Shir63]

 Hi Guys,

I have an issue with a formula that is meant to calculate % of discount.

ORDER NUMBER	List Price	Discount Amount	is Discount	Stream	Name	% of Discount Calculated
1	100	20	Y	HW	A	0.12
1	50	10	Y	Con	B	0.12
1	0	10	N	HW	C	0.12
1	100	30	N	HW	D	0.12
	Sum List Price	250
	Sum Discount = Y	30
	% of Discount	0.12

based on an order # I want to sum all Discount = Y, & all list price inside an order

receive a % , and give it to each of the lines inside that order.

then multiply that % in the list price.

this is the formula i created: 

avg(total <[Sale Order Order Number]>aggr(sum(total <[Sale Order Order Number]> {1<[IS Discount (Y/N)] = {'Y'} >} $(vDiscount))
/
sum(total <[Sale Order Order Number]>{1} $(vList2)),[Sale Order Order Number]))

*

Sum($(vList2))

the issue is that it only works in a table with the dimension "Sale Order Order Number".

it doesn't work in a pivot, or with a different dimension.

what do you think i should do? appreciate your help guys.

thanks!

[anthonyj]

Hi @Shir63 ,

I think what's happening is that if you change your dimension from [Sale Order Order Number] then your aggregation will no longer be correct because Qlik is now aggregating by that column and the new dimension you've added.

In your aggr( ) you're calling out [Sale Order Order Number] as your grouping which makes sense if that is also your dimension. I presume by adding a different dimension you would then want to see the breakdown by [Sale Order Order Number] and the new column. If that's the case you can try adding this new column name to your total <[Sale Order Order Number]> statements.

Regards

Anthony