Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Distinct Set Analysis

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

4010
0014010

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

1 Solution

Accepted Solutions
richard_pressan
Contributor

Re: Distinct Set Analysis

Try this:

Sum(

     Aggr(

          DISTINCT

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

          ,

          Article.Artikelnr

          )

)

5 Replies
MVP
MVP

Re: Distinct Set Analysis

Hi,

Remove the duplicate rows in script by using

LOAD DISTINCT

*

FROM Datasource;

OR

Use this expression

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

Hope this helps you.

Regards,

Jagan.

Not applicable

Re: Distinct Set Analysis

Try

avg(Artikel.Stocklevel * Artikel.Listprice)

or

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

Not applicable

Re: Distinct Set Analysis

try this

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

Not applicable

Re: Distinct Set Analysis

Thanks for the reply.

However this did not solve our problem;

i have pasted a picture to show you the phenomenon we are facing Distinct Count.png

richard_pressan
Contributor

Re: Distinct Set Analysis

Try this:

Sum(

     Aggr(

          DISTINCT

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

          ,

          Article.Artikelnr

          )

)

Community Browser