topic Agregation by [Order - Position] in App Development

Agregation by [Order - Position]

jorditorras — Wed, 29 Apr 2020 17:10:45 GMT

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.

Re: Agregation by [Order - Position]

Oleg_Troyansky — Wed, 29 Apr 2020 21:03:12 GMT

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,

Re: Agregation by [Order - Position]

jorditorras — Wed, 29 Apr 2020 22:04:56 GMT

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

Re: Agregation by [Order - Position]

jorditorras — Thu, 07 May 2020 08:42:05 GMT

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!!!!

Re: Agregation by [Order - Position]

Oleg_Troyansky — Fri, 08 May 2020 02:29:40 GMT

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,