topic dimension using Valuelist with two equal values in QlikView

dimension using Valuelist with two equal values

Tue, 20 Dec 2011 16:29:58 GMT

i need to create a straight table with the next structure

Concept Expression1 Expression2 Expression3

Sales - - -

2010 1000 2000 3000

2011 2000 3000 4000

Cost - - -

2010 3000 4000 5000

2011 4000 5000 6000

i have created the sales structure, i used the valuelist in my dimension like this

valuelist('Sales',$(=vAnio_Anterior),$(=vAnio_Actual),'cost')

and all found well, but if i put the next value in my dimension, the report does not work, i mean i put this dimension

valuelist('Sales',$(=vAnio_Anterior),$(=vAnio_Actual),'cost',$(=vAnio_Anterior))

and Qlikview doesn't show the 2010 value, i have tried whith this

valuelist('Sales',$(=vAnio_Anterior),$(=vAnio_Actual),'cost',$(=vAnio_Anterior) & ' Cost')

but the program frozen up, i don't know what happen, someone know how i can do this

dimension using Valuelist with two equal values

swuehl — Tue, 20 Dec 2011 17:19:42 GMT

I believe your synthetic dimension values should be unique, or how is QV supposed to know which value to choose if you query for e.g. 2011 Cost?

I think you could use two synthetic dimensions e.g. in a pivot table, first like

=valuelist('Sales','Cost')

second

=valuelist($(=vAnio_Anterior),$(=vAnio_Actual) )

then in an expression you can query both dimension values by stating the exact valuelist phrases again.

But maybe you don't even need the synthetic dimensions, I think you can also limit e.g. Year dimension just by the use of a calculated dimension, and maybe you already have a FigureType field with values 'Sales' and 'Cost'?

dimension using Valuelist with two equal values

vgutkovsky — Tue, 20 Dec 2011 17:23:03 GMT

Yikes, that's a mess! If you want to leave the chart like this, I would suggest creating a separate island dimension:

expression_island:

LOAD * INLINE [

EXP_island

Sales

Cost

];

Then your chart would look something like this:

Dimensions: Exp_island, Year

Expression #1:

if(EXP_island='Sales',sum({<Year={">=$(vAnio_Anterior)"}>}Sales),sum({<Year={">=$(vAnio_Anterior)"}>}Cost))

Regards,

Vlad