Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

achakilam1022
Contributor

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

Tags (2)
1 Solution

Accepted Solutions

Re: Aggregate in Set Analysis QlikSense

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

4 Replies
MVP
MVP

Re: Aggregate in Set Analysis QlikSense

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

achakilam1022
Contributor

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(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
Contributor

Re: Aggregate in Set Analysis QlikSense

Thanks Celambarasan. This works perfect.

I appreciate your help.

Community Browser