4 Replies Latest reply: Oct 3, 2017 11:01 AM by Amuktha Chakilam

# 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:

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({\$<Status={'ok'}>}aggr(cost, id)))/

• Modified Price=

Results:

Thanks,

Amuktha

• ###### Re: Aggregate in Set Analysis QlikSense

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

• ###### Re: Aggregate in Set Analysis QlikSense

Hi Sunny,

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

Thanks

• ###### Re: Aggregate in Set Analysis QlikSense

Amuktha,

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

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