topic aggr and dimensions in QlikView

aggr and dimensions

Tue, 11 Jan 2011 14:01:52 GMT

Hello im having some issues with AGGR

the table below has sales for different weeks and stores

LOAD * INLINE [

sales ,week ,store

0 ,1 ,vbg

6 ,2 ,vbg

0 ,2 ,vbg

8 ,3 ,vbg

0 ,1 ,gbg

0 ,2 ,gbg

8 ,3 ,gbg

];

I create a Straight Table with dimension: store

expression: sum( aggr( if( sum( sales ) > 0,1,0) , week))

expression showing the number of weeks the sale is positive.

Result: the first store is shown correct in the table but the rest is plain zeros and if you make a selection on one of the zeroed stores it will be shown correct as well.

What is happening ? ? ?

aggr and dimensions

lironbaram — Tue, 11 Jan 2011 14:09:42 GMT

hei

attach is an answer to the problem

when you use aggr , you have to include all the filed you want the aggr to use

the function is diregarding the dimention

so the expression you wrote calculeted the aggr for each week for all the stores together

hope its helps you

aggr and dimensions

Tue, 11 Jan 2011 14:29:00 GMT

I would like to disagree.

When following my example the store vbg show 2 weeks which is correct and gbg 0 which is wrong. ()

If what you state would be true they both would show 3, am I right?

I smell a bug here but if anyone has an explanation for this behaviour it would be great! 😃

aggr and dimensions

lironbaram — Tue, 11 Jan 2011 14:39:03 GMT

may be i wasnt clear

when you use your formula then it clcluate it only one time for the first row usually

the result is correct to this line but other lines get zero

did my answer helped you

aggr and dimensions

Tue, 11 Jan 2011 17:41:47 GMT

Hi cathabis,

If you use this:

Dimension => "store"

Expression => "=sum( aggr( if( sum( sales ) > 0,1,0) , week))"

It shows 2 cause in week 2 and week 3 you have any store with sales > 0. If you had any store in week 3 it would show 3

You need to use the Dimension "store" in the aggr function if you want to show rigthly the results for every "store".

I hope it hepls.

aggr and dimensions

Oleg_Troyansky — Tue, 11 Jan 2011 18:48:41 GMT

Try playing with qualifiers "DISTINCT" and "NODISTINCT". The behavior that you are describing, matches the way "DISTINCT" works - try adding qualifies NODISTINCT inside the AGGR(). This way, the AGGR will return multiple results, for different stores.

aggr and dimensions

Wed, 12 Jan 2011 09:35:43 GMT

EduardoGonzalez

ok i buy that but how come the same result is not shown in other rows except the first?

aggr and dimensions

Wed, 12 Jan 2011 18:06:24 GMT

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

Hi,

In your script you have

sales ,week ,store

0 ,1 ,vbg

6 ,2 ,vbg

0 ,2 ,vbg

8 ,3 ,vbg

0 ,1 ,gbg

0 ,2 ,gbg

8 ,3 ,gbg

And you´ll get this

Vbg '2'

Cause there are two weeks where any store have sales > 0, and in the week 2 the first row with sales > 0 belong to 'vbg' and the same for the Week 3

If you change the rows order in your script, getting for example something like this:

sales ,week ,store

0 ,1 ,vbg

6 ,2 ,vbg

0 ,2 ,vbg

0 ,1 ,gbg

0 ,2 ,gbg

8 ,3 ,gbg

8 ,3 ,vbg

You´ll get this

gbg '1'

vbg '1'

Cause now in week 2 the first row with sales > 0 belong to 'vbg' and in week 3 the first row with sales > 0 belong to 'gbg'

I think it´s what is happend.

aggr and dimensions

Oleg_Troyansky — Wed, 12 Jan 2011 18:53:43 GMT


calthabis wrote:ok i buy that but how come the same result is not shown in other rows except the first?

This is just the way AGGR works within charts