Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
Do it has any primary key to identify the single combination of INB_SIBSilo and CI_SILOmschrijving?
if so
Sum({<PrimaryKeyFieldName={"=INB_SIBSilo = CI_SILOmschrijving"}>} CI_SC_Hoeveelheid)
The 2 tables from which the fields come from are linked by a field called Lotnumber.
The INB_ field represents the SILO from which the production qty is taken. The CI_SIL* field holds the Silo on which the correction was made. The detail report is sorted by product,lotnumber and Silo, the summed qty's have to be [production qty] and [correction qty]. Hope this helps...
Then you could use the Lotnumber in the place of PrimaryKeyFieldName
will look like
Sum({<Lotnumber={"=INB_SIBSilo = CI_SILOmschrijving"}>} CI_SC_Hoeveelheid)
Unfortunately this does not work. You may not fully understand what I am trying to achieve.
See example picture below: As you can see my the product/lot and silo have qty ordered. for location 'Kaai 01' there was a correction. Now instead of having that shown on every line, I just want to have the qty corrected displayed on the line where silo = CI SILOmschrijving. I had the qty corrected in a set analysis function, but as soon as I would take out the Lotnumber filter to look at the entire table it would not show the qty corrected anymore. Using the sum-if would mean the column would not show a total qty corrected anymore. Hope this explains what I mean better....