topic Re: Sum over unique rows in expression in QlikView

Sum over unique rows in expression

Tue, 02 Jun 2015 08:12:27 GMT

Hi all,

I want to calculate the percentage of stock that is obsolete in a gauge chart. I have two tables, one with the stock levels and one with sales.

The stock table looks as follows:

product stock obsolete

A 300 YES

B 200 NO

C 200 NO

D 300 NO

I also have the following sales table:

product sold

B 20

B 30

B 45

C 10

C 100

D 150

When I now calculate the sum in a graph expression, it contains many duplicates, I presume that this is the case due to Qlikview making the one-to-many relation between these tables (as expected in the rest of the dashboard, but not what I want here..).

The result here should be the sum of obsolete stock, divided by the total stock. In this case: 300/(300+200+200+300) = 30%.

I have been messing around with aggregate, total, distinct, sum, min, but I cannot seem to get it right..

Could anybody help me or point me in the right direction?

Thanks in advance,

Florian

Re: Sum over unique rows in expression

Gysbert_Wassenaar — Tue, 02 Jun 2015 08:15:11 GMT

Try sum({<obsolete={'YES'}>}stock)/sum({1}total stock)

Re: Sum over unique rows in expression

Tue, 02 Jun 2015 08:27:25 GMT

Thanks, this works! This was a simplified example of something that I was actually trying to achieve, in which it does not work yet. So I probably have to look at the structure of the data there. sum({1}total stock) returns a value that is way too high there..

Re: Sum over unique rows in expression

Colin-Albert — Tue, 02 Jun 2015 08:28:57 GMT

Using the {1} set will mean that all data selections are ignored, which is why you are seeing a high figure.

Try this, so the same selection apply to both expressions.

sum({<obsolete={'YES'}>}stock) / sum(total stock)

Re: Sum over unique rows in expression

Gysbert_Wassenaar — Tue, 02 Jun 2015 08:29:21 GMT

The {1} makes the expression ignore all selections. If that's not what you need then try removing it.

Re: Sum over unique rows in expression

Tue, 02 Jun 2015 08:42:04 GMT

Thanks again for your suggestions, but I think I am doing something more fundamental wrong. Both sum({1}total stock) and sum(total stock) return a value that is about 320 times as high as the total stock actually is. Exporting the table (product, stock) to excel shows that the sum should be around 50.000, while the sum({1}total stock) returns something like 13.000.000.

Re: Sum over unique rows in expression

Colin-Albert — Tue, 02 Jun 2015 09:09:30 GMT

What do you get from this expression?

sum({<obsolete={'YES'}>}stock) / sum(stock)

Re: Sum over unique rows in expression

Tue, 02 Jun 2015 09:19:52 GMT

Just sum(stock) gives a value of about 20.000, which is too low. I have attached my data structure, maybe that gives you an idea of why these strange values are returned? If I create a table of 'newFinal', I get exactly the information I expect, and the total of the stock column is about 50.000 there, which is the value it should be.

Also, by making a selection, the value for sum(stock) can actually become higher?

Re: Sum over unique rows in expression

Colin-Albert — Tue, 02 Jun 2015 09:41:05 GMT

I would suggest you remove the synthetic keys as it will make your data model easier to understand and test. With the synethic keys removed you will be able to preview the tables correctly and see if the data associations are correct.

Re: Sum over unique rows in expression

Tue, 02 Jun 2015 11:12:22 GMT

Thanks for this tip! I managed to find the mistake in my document. I forgot to add 'distinct' to a load, where I should have added it. This caused some combinations to show up more than once in a table, but not in the table box.

Thanks to you both for the help.

Re: Sum over unique rows in expression

Colin-Albert — Tue, 02 Jun 2015 12:23:16 GMT

Great, glad you've fixed the problem

If you can mark any helpful and correct answers to close the post this will help others searching on a similar issue.