topic Re: Move from Sum(If) to sum(set analysis) in QlikView

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

andre_ficken — Thu, 21 Nov 2013 12:22:19 GMT

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

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

CELAMBARASAN — Thu, 21 Nov 2013 12:58:25 GMT

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)

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

andre_ficken — Thu, 21 Nov 2013 13:10:06 GMT

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

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

CELAMBARASAN — Thu, 21 Nov 2013 13:16:35 GMT

Then you could use the Lotnumber in the place of PrimaryKeyFieldName

will look like

Sum({<Lotnumber={"=INB_SIBSilo = CI_SILOmschrijving"}>} CI_SC_Hoeveelheid)

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

andre_ficken — Thu, 21 Nov 2013 13:38:56 GMT

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