0 Replies Latest reply: Sep 26, 2017 2:46 PM by Amuktha Chakilam

# Set Analysis formula for Sales (QlikSense)

Hi all,

The sales data is in the following format:

Sales:

1, 1, 1, 0.5, X, 0.1

1, 1, 1, 1.5, Y

1, 2, 2, 2, X, 0.1

1, 2, 2, 1.5, Y,

1, 2, 2, 1, Z, 0.16

1, 3, 3, 4, X, 0.1

2, 4, 4, 3.5, X, 0.1

2, 4, 4, 1, A,

2, 5, 5, 6, B,

2, 6, 6, 3, X, 0.1

2, 6, 6, 3, Y];

I have calculated revenue and Profit using the following formulas (Analysis by buyer)

Now, I want to use the adjusted_profit_margin(this is specific to buyer) and calculate the same metrics across all buyers in a category

• new revenue = reported revenue * (1 – reported_profit_margin_of_the_entire_category) * (1 + adjusted_profit_margin)

• new gross profit = reported revenue * (1 – reported margin of the category) * ( adjusted_profit_margin)

`If adjusted profit margin is null then revenue inst modified`.

For example: in category 1:

reported cost: 6; reported revenue: 10.5; reported profit margin of category 1: 0.43

new revenue from category 1 : [6.5*(1-0.43)*(1+0.1)]+ [3] + [1*(1-0.43)*(1+0.16)]