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

    Set Analysis formula for Sales (QlikSense)

    Amuktha Chakilam

      Hi all,

       

      The sales data is in the following format:

       

      Sales:

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

      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)

       

      Reported Revenue: sum(aggr(price,id,buyer))

       

      Reported Profit Margin: (sum(aggr(price,id,buyer))-sum(aggr(cost, id)))/sum(aggr(price,id,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)]


      Question: Please help me in writing formulas for new revenue and new gross profit.

       


      Thanks,

      Amuktha