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.
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.
