4 Replies Latest reply: Nov 21, 2013 8:38 AM by André Ficken RSS

    Move from Sum(If) to sum(set analysis)

    André Ficken

      I am trying to resolve the following issue:

       

      My data model is complex and at some stage I need to sum received qty's (table 1) look at the applied corrections (table 2) and compare the 2 combined with the qty that was booked in the financial system (table 3)


      Table 1 and table 2 are related by Lotnumber, so I can sum qty received and qty corrected by lot. That works fine on the straight forward sum().

       

      In my detail overview of the receipt I want to show all silos in which the product was received. The corrections are recorded by silo too.

      The SIBSilo is the destination Silo of the receipt. the CI_SILOmschrijving is the SILO on which the correction was applied.

       

      When I use: if (INB_SIBSilo = CI_SILOmschrijving, sum(CI_SC_Hoeveelheid),0)

      The qty is correct, but it does not sum() on the totalby lotnumber. My guess is that a sum with set analysis will sum correctly and show the total line sum as well. The only problem is that I am struggling a bit with the syntax of the set analysis.

       

      Can someone help me with the syntax for the set analysis for this sum-if so I can replace it in my detail report??

      Thanks for your help in advance!!