2 Replies Latest reply: Sep 20, 2017 1:24 PM by Amuktha Chakilam RSS

    Column total incorrect in a table (QlikSense)

    Amuktha Chakilam

      Hi all,


      I'm calculating a few measures using the following formulas.


      Total Sales = sum(aggr(price, id, buyer))

      Total Cost= sum(aggr(cost, id))

      Reported Profit Margin= (Total Sales- Total Cost)/Total Sales


      Now, a few buyers have a "adjusted profit margin" value and rest have null. Using this field I'm calculating New Sales as:

      New Sales= Total Sales / ((1+ Reported Profit Margin) * (1 + Adjusted Profit Margin)) =

      if(IsNull(adjusted_profit_margin), sum(aggr(price, id, buyer)),

      (sum(aggr(price, id, buyer))) /

      ((1 + if(sum(aggr(price, id, buyer))='0',0,(sum(aggr(price, id, buyer))-sum(aggr(cost, id)))/(sum(aggr(price, id, buyer))))) * (1 + adjusted_profit_margin)))

      when I calculate the new sales measure, the individual New Sales values of the buyers are correct but the column total ( of New Sales)  is same as the Total Sales. I'm confused  about why this is happening.

      I appreciate any help with this. Please let me know if I need to modify anything or if there is any better way to do this.