Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Shir63
Contributor III
Contributor III

Creating Avg column based on if and group by

hello guys,

i have these columns:

Sale order

Discount Amount

Price List Amount

is_Discount_Flag

i need to create = Discount amount / Price list amount when is_discount_flag = 'Y'

and i want it to apply on all the rows within the order

sale orderPrice list Amount Discount Amountis discount flagAvg Discount
11110050Y0.35
11100N0.35
11100N0.35
11120055Y0.35
22215030N0.01666667
22235010Y0.01666667
22200N0.01666667
2221000Y0.01666667
33330015Y0.05

 

i have tried this formula:

sum(total aggr(sum( {<[IS Discount Flag] = {'Y'}>} [Discount Amount])),[Order Number])
/

sum(total([List Price Adjusted] ))

and the formula works, but when there is no order number selected, it presents the total of all orders to all rows.

meaning it takes the total discount / total list price and present it to all my rows

and i need it to stay frozen -  always show the avg discount based on the order, no matter what my selections are

appreciate your help

Labels (1)
1 Solution

Accepted Solutions
lironbaram
Partner - Master III
Partner - Master III

hi this should work for you 

sum({<[IS Discount Flag] = {'Y'}>} total <[Order Number]> [Discount Amount])
/
sum(total <[Order Number] >([List Price Adjusted] ))

View solution in original post

2 Replies
lironbaram
Partner - Master III
Partner - Master III

hi this should work for you 

sum({<[IS Discount Flag] = {'Y'}>} total <[Order Number]> [Discount Amount])
/
sum(total <[Order Number] >([List Price Adjusted] ))
Shir63
Contributor III
Contributor III
Author

thank you, that worked!

appreciate it