2 Replies Latest reply: Jan 30, 2017 11:39 AM by Giovanni Leonardi RSS

    Aggregation in Qlik Sense shows unexpected values

    Giovanni Leonardi

      Hello everybody, this is my first post and I come to you with a smart question.

       

      I'm searching for a solution to this problem: I have a set of data coming from stock balances. My app must show the amount of available quantities for each Item.

       

      The simplest expression would be [Stock Qty] - [Ordered Qty] = [Available Qty] that will be coded as  Sum ([Stock Qty] - [Ordered Qty]).


      Now comes the tricky part. Since I don't want any negative value my expression should be:


      if ((Sum ([Stock Qty] - [Ordered Qty]) < 0, 0, Sum ([Stock Qty] - [Ordered Qty])) and at the lower aggregation level it works fine, but when it comes to do it to a higher level it doesn't make the job done.


      I have this scenario:

       

      WarehouseItemStock BalanceOrdered QtyAvailable Qty
      MAINA10120
      MAINB15141
      MAINC202
      Total27263
      SECONDARYB105

      5

      SECONDARYC5110
      Total15165


      If I create a pivot table or a Bar Chart based on Warehouse field in Dimension and Available Qty field in measure, the result is the following:

       

      WarehouseAvailable Qty
      MAIN1
      SECONDARY0


      But i would like to be:

       

      Warehouse
      Available Qty
      MAIN3
      SECONDARY5

       

      Is there a way to tell the system to always sum at the lowest level to obtain the above values.

       

      I hope I made myself clear.

       

      Regards,

      Giovanni