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

    Aggregate in Set Analysis QlikSense

    Amuktha Chakilam

      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