topic Re: Distinct Set Analysis in QlikView

Distinct Set Analysis

Tue, 14 May 2013 11:39:52 GMT

Hi!

i would like to setup the follwing set analysis statement for calculation.

i have three objects;

1Artikel.Artikelnr

2Artikel.Stocklevel

3.Artikel.Listprice

In my QV app Artikel.Artikelnr can appear multiple sames on the same nr.

Artnr	Stocklevel	Listprice
001	40	10
001	40	10

there my calculations are wrong, it should be 400 not 800.

so what i am trying to achieve is the following

sum(distinct(Article.Artikelnr(Artikel.Stocklevel * Artikel.Listprice))

it is however still returning values that are too high.

is there anotherway to wrtie the statement, set analysis perhaps that can be used in a pivot table and still be dynamic?

Best,

Bradley

Re: Distinct Set Analysis

jagan — Tue, 14 May 2013 11:44:45 GMT

Hi,

Remove the duplicate rows in script by using

LOAD DISTINCT

FROM Datasource;

Use this expression

sum(Aggr(Max(Artikel.Stocklevel) * Max(Artikel.Listprice), Article.Artikelnr))

Hope this helps you.

Regards,

Jagan.

Re: Distinct Set Analysis

Tue, 14 May 2013 11:46:55 GMT

Try

avg(Artikel.Stocklevel * Artikel.Listprice)

sum(Artikel.Stocklevel * Artikel.Listprice) / count(Article.Artikelnr)

Re: Distinct Set Analysis

Tue, 14 May 2013 11:56:50 GMT

try this

Aggr(sum(Artikel.Stocklevel * Artikel.Listprice), Article.Artikelnr)

Re: Distinct Set Analysis

Tue, 14 May 2013 12:13:15 GMT

Thanks for the reply.

However this did not solve our problem;

i have pasted a picture to show you the phenomenon we are facing

Re: Distinct Set Analysis

rlp — Tue, 14 May 2013 13:33:15 GMT

Try this:

Sum(

Aggr(

DISTINCT

Max(Artikel.Stocklevel) * Max(Artikel.Listprice)

Article.Artikelnr

)