topic Re: Valuelist and Aggr in QlikView

Valuelist and Aggr

fabio — Thu, 05 Dec 2013 08:02:25 GMT

hi,

chart with a dimension:

=ValueList('a','b','c')

and the expression:

if (ValueList('a','b','c')='a',

'aa',

if (ValueList('a','b','c')='b',

'bb',

if (ValueList('a','b','c')='c',

'cc' ) ))

work!

the result is a table like this:

a aa

b bb

c cc

i change the expression in this way:

if (ValueList('a','b','c')='a',

'aa',

if (ValueList('a','b','c')='b',

count(

aggr ( if ( $(vFattProgrEse) =0 and $(vFattProgrEse1) = 0 and $(vFattProgrEse2) > 0, Count ( distinct ST_CLIENTE)),ST_CLIENTE)

)

if (ValueList('a','b','c')='c',

'cc' ) ))

the aggr expression count the customers filtered in a certain way. the single expression work in others chart.

But in this chart with the "valuelist dimension" the result is:

a aa

c cc

the 'b' values is missing.

if i change the order in this way:

if (ValueList('a','b','c')='a',

count(

aggr ( if ( $(vFattProgrEse) =0 and $(vFattProgrEse1) = 0 and $(vFattProgrEse2) > 0, Count ( distinct ST_CLIENTE)),ST_CLIENTE)

)

if (ValueList('a','b','c')='b',

'bbì'

if (ValueList('a','b','c')='c',

'cc' ) ))

the result is

a 236

b bb

c cc

this work!

why?!?!?

this is just a basic example,to understand how it works

thanks in advance

Re: Valuelist and Aggr

juleshartley — Thu, 12 May 2016 12:06:17 GMT

I have this same issue - what a nuisance!

Re: Valuelist and Aggr

swuehl — Thu, 12 May 2016 12:10:18 GMT

Try

if (ValueList('a','b','c')='a',

'aa',

if (ValueList('a','b','c')='b',

count(

aggr (NODISTINCT if ( $(vFattProgrEse) =0 and $(vFattProgrEse1) = 0 and $(vFattProgrEse2) > 0, Count ( distinct ST_CLIENTE)),ST_CLIENTE)

)

if (ValueList('a','b','c')='c',

'cc' )

))

if (ValueList('a','b','c')='a',

'aa',

if (ValueList('a','b','c')='b',

count( TOTAL aggr (if ( $(vFattProgrEse) =0 and $(vFattProgrEse1) = 0 and $(vFattProgrEse2) > 0, Count ( distinct ST_CLIENTE)),ST_CLIENTE)

)

if (ValueList('a','b','c')='c',

'cc' )

))

Re: Valuelist and Aggr

Clever_Anjos — Thu, 12 May 2016 12:11:43 GMT

Are there more dimensions?

Re: Valuelist and Aggr

Clever_Anjos — Thu, 12 May 2016 12:12:22 GMT

Would you mind sharing this variables expressions?

vFattProgrEse

vFattProgrEse1

vFattProgrEse2

Re: Valuelist and Aggr

swuehl — Thu, 12 May 2016 12:15:18 GMT

Clever, the original thread is 2 years old, I doubt that we get an answer from Fabio.

I've answered to Julian Hartley, using the original expressions.

Re: Valuelist and Aggr

Clever_Anjos — Thu, 12 May 2016 12:46:06 GMT

Sorry

Re: Valuelist and Aggr

juleshartley — Thu, 23 Jun 2016 11:17:55 GMT

Thanks swuehl, you're right... that does fix it! Unfortunately it then gives me incorrect numbers so I guess I'll need to adjust dataset to only feed single values...