Aggregate in Set Analysis QlikSense

[achakilam1022]

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:

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

I appreciate your help

Thanks,

Amuktha

[sunny_talwar]

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

[achakilam1022]

Hi Sunny,

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

Thanks

[CELAMBARASAN · Accepted solution]

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]

Thanks Celambarasan. This works perfect.

I appreciate your help.