
Re: Using sum if formula in Qlikview
Paul Krolicki May 6, 2014 7:55 AM (in response to Ruben Heijn)How the formula in your Excel looks like?

Re: Using sum if formula in Qlikview
Ruben Heijn May 6, 2014 8:02 AM (in response to Paul Krolicki )=sum.if(stuklijst!$E:$E;$A:$A;stuklijst!$K:$K)
Stuklijst!$E:$E = the productnumber
$A:$A= the productnumber
stuklijst!$K:$K= the bought goods


Re: Using sum if formula in Qlikview
Pierre de Calan May 6, 2014 8:55 AM (in response to Ruben Heijn)Hi,
I think it could work with a pivot chart in QlikView
Your dimension comes from productnumber: all product numbers, or only part of them (to have only products 1, 2 and 4 : if(match(productnumber,1,2,4),productnumber) then tick Hide null values), or grouped by category : if(match(productnumber,1,2,4),'1st Category','2nd Category).
Your expression can be Sum(RealCosts)Sum(CalculatedCosts), or Avg(RealCostsCalculatedCosts), etc.
The rest depends on the result you're precisely looking for.
Hope it helps.

Re: Using sum if formula in Qlikview
Ruben Heijn May 8, 2014 5:10 AM (in response to Pierre de Calan)I've tried it but i can't make it work properly. There seems some error with the calculated costs. For some reasons it takes the whole calculated costs for each product.
So for example: product 1 has been bought 1000 times, the calculaten, as given by qlikview, is the total of 1.400.000 times (total of calculated costs for every product).
For product 2 has a real costs of 400 times, but again the calculated costs gives 1.400.000 times.
I think i can make it work for 1 product by filling in manual the product code. But for close to a thousand products this seems to be ineffcient.
Anyone knows how to try to tackle this problem?

Re: Using sum if formula in Qlikview
Ashwani Kumar May 9, 2014 5:00 AM (in response to Ruben Heijn)Hi,
Can you post a sample Data?

Re: Using sum if formula in Qlikview
Ruben Heijn May 13, 2014 8:16 AM (in response to Ashwani Kumar)Hey,
I wasnt really sure how to post an Excel sheet (if it's possible). But here is an image. The data above is the data retrieved from our supplier. The data below is the bills of material.
I want to use the blue marked columns: When ARTIKELNR is the same as productcode i want to retrieve the inkoop data.
I have worked a bit and atm i use the formula: if(match(ARTIKELNR, productcode), inkoop)
I think i can retrieve some data from it, however i don't get a total from it. I get each individual inkoop of the products (this sample contains just 1 product). Also the loading time seems quite big.



