Agregation by [Order - Position]

[jorditorras]

Hi Qlik Gurus, 

I'm developing an Application in QlikSense and I have to calculate "Pending Quantity to Receive" of the "Document - Positions" in a Purchasing Scenario. Then my calculation should be [Purchase Order Quantity] - [Quantity Received]. The problem is that if in the App I create a table and I analyze "Pending Quantity to Receive" by "Company", the result is different than if I analyze it by "Document - Position". I want the calculation to be done aggregated for every "Document - Position" to see the same result if I check this KPI by Document, Company or any other Dimension. 

Could you please help me with this calculation please? 

Thanks.

[Oleg_Troyansky · Accepted solution]

Hi,

If I understand your need correctly, you need to calculate the difference between PO Qty and Received Qty, aggregated by "Document - Position" rather than for the whole company. In this case, you need to use the advanced aggregation function AGGR() with "Document - Position" as a dimension. Something along these lines:

sum(

     AGGR(

               RangeMax(0, sum(POQty) - sum(ReceivedQty))

               , [Document - Position)

)

I added the RangeMax() function to only include positive balances - in case you might have order with Received qty that it higher than the PO Qty.

I think this calculation should work for your needs.

Cheers,

[jorditorras]

Wowwww Lots of Thanks!!!! It work perfectly!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! 

[jorditorras]

Hello!

Now I'm seeing that I have a problem if I see this data in a Pivot Table, as when I analyze Totals by Document, it's correct. But when I analyze totals by Company or any other dimension, then the result is wrong.... This is only happening in Pivot Table. If I see use this formula in a straight table and I see the data by Company, the result is OK. 

Is it possible to adapt this formula to be able to see the correct totals by any dimension in a Pivot Table?

Thanks!!!!

[Oleg_Troyansky]

Hi!

In order to use the AGGR() function in a different chart, you need to add all other chart dimensions into the list of the AGGR dimensions. Then it will work.

Best,