Using sum if formula in Qlikview

[#1]

Dear sir,madam,

I am developing a dashboard in Qlikview.

I would like to use the formula 'sum if' for making an analysis.

I already made an analysis in Excel, with the sum if formula. I am  working currently on a way to automatically show the difference between the real cost of sales compared to the calculated costs of sales.

Does somebody have any experience with solving this issue?

Thanks in advance for the response.

Kind regards,

Ruben Heijn

[#2]

How the formula in your Excel looks like?

[#3]

=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

[#4]

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(RealCosts-CalculatedCosts), etc.

The rest depends on the result you're precisely looking for.

Hope it helps.

[#5]

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?

[ashwanin]

Hi,

Can you post a sample Data?

[#7]

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.

Am i using a right formula?

[robert_mika]

When loading your data you have to change the artiklen to productcode to create a key

LOAD Artikelnr as productcode,

     ...

FROM

...

Then use this expression

sum({$<Artikelnr ={'productcode'}>}inkoop)

(I have chosen random data)