Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
achakilam1022
Creator II
Creator II

Aggregate in Set Analysis QlikSense

Hi all,

I need some guidance with the following Profit Margin of Category expression. I'm trying to aggregate the profit margin by category but the expression is failing to do the same.

Sales:

load * inline [category, id, cost, price, buyer, adjusted_profit_margin,Status

1, 1, 1, 0.5, X, 0.1,ok

1, 1, 1, 1.5, Y, ,ok

1, 2, 2, 2, X, 0.1,ok

1, 2, 2, 1.5, Y, ,ok

1, 2, 2, 1, Z, 0.16,ok

1, 3, 3, 4, X, 0.1,ok

2, 4, 4, 3.5, X, 0.1,ok

2, 4, 4, 1, A, ,ok

2, 5, 5, 6, B, ,ok

2, 6, 6, 3, X, 0.1,ok

2, 6, 6, 3, Y, ,ok];

I need to modify the price field using adjusted_profit margin   based on formula

  • new price=price * (1 – profit margin of entire category) * (1 + adjusted_profit_margin)

where Profit Margin of a category = (Total Sales of Category- Total cost of unique ids' in that category) /Total Sales of Category


  • I'm calculating profit margin of category using this expression:

ProfitMargin=

sum(aggr((sum({<Status={'ok'}>}aggr(price,id,buyer))

-sum({$<Status={'ok'}>}aggr(cost, id)))/

(sum({<Status={'ok'}>}aggr(price,id,buyer))),category))

  • Modified Price=

if(len(trim(adjusted_profit_margin))=0,price,price*(1-$(ProfitMargin))*(1+adjusted_profit_margin))

Results:

IR_price.PNG


Please help me in figuring out why aggregation fails in the Profit Margin of Category expression

I appreciate your help

Thanks,

Amuktha

1 Solution

Accepted Solutions
CELAMBARASAN
Partner - Champion
Partner - Champion

Amuktha,

Use Total within the Sum(), have a look at the below expression

sum(Total<category> aggr((sum({<Status={'ok'}>}aggr(price,id,buyer))

-sum({$<Status={'ok'}>}aggr(cost, id)))/

(sum({<Status={'ok'}>}aggr(price,id,buyer))),category))

-Celambarasan

View solution in original post

4 Replies
sunny_talwar

Would you be able to share your Excel file with us here?

achakilam1022
Creator II
Creator II
Author

Hi Sunny,

I've attached the Excel file to the original post.

Thanks

CELAMBARASAN
Partner - Champion
Partner - Champion

Amuktha,

Use Total within the Sum(), have a look at the below expression

sum(Total<category> aggr((sum({<Status={'ok'}>}aggr(price,id,buyer))

-sum({$<Status={'ok'}>}aggr(cost, id)))/

(sum({<Status={'ok'}>}aggr(price,id,buyer))),category))

-Celambarasan

achakilam1022
Creator II
Creator II
Author

Thanks Celambarasan. This works perfect.

I appreciate your help.