topic valuelist in QlikView

valuelist

Tue, 19 Jul 2011 08:26:59 GMT

Hello everyone,

I have a problem with a table using calculated dimensions with valuelist.

What I want to display is a certain value (income) over each month for a selected year and the corresponding prior year.

My table has 1 dimension:

=valuelist($(=Only(Year)),$(=Only(Year)-1))

Then for each month I have a column in which I want to calculate the income for the present and for the past year (the 2 lines of the dimension). I tried the following:

aggr(count({<Month={Jan}>}Income),valuelist($(=Only(Year)),$(=Only(Year)-1)))

Unfortunatelly this does not work. With the above shown formula I always get the income value for January of the present year in both lines (present and past year).

Could anyone help me out with this and tell me what am I doing wrong?

Thanks a lot!

valuelist

Tue, 19 Jul 2011 08:57:35 GMT

Hey again,

I have been searching around some more and I have found a post where they described the if-statement for the use of valuelists.

I have now changed my expression to the following (my dimension is still the same):

if(valuelist($(=Only(Jahr)),$(=Only(Jahr)-1))=$(Only(Jahr)),count({$<Jahr={$(=Only(Jahr))},Monat={Jan}>}UmsatzBestZeile),

if(valuelist($(=Only(Jahr)),$(=Only(Jahr)-1))=$(Only(Jahr)-1),count({$<Jahr={$(=Only(Jahr)-1)},Monat={Jan}>}UmsatzBestZeile),''))

Unfortunatelly this doesn't work either. Now I get no values at all in my fields.

If I try to use fixed values in the valuelist (e.g. valuelist(2009,2008)) then it does work alright. Is it not possible to keep the valuelist flexible with variables?

Does anyone have any ideas with this? Thanks a lot!

Re: valuelist

hdonald — Tue, 19 Jul 2011 15:19:43 GMT

Hi,

It could be that you're missing some '=' symbols before the odd 'only'

I copied your syntax on a test qvw and it worked fine

e.g.

=if(valuelist($(=Only(Year)),$(=Only(Year)-1))=$(=Only(Year)),sum({$<Year={$(=Only(Year))},Month={Jan}>}LineSalesAmount),

if(valuelist($(=Only(Year)),$(=Only(Year)-1))=$(=Only(Year)-1),sum({$<Year={$(=Only(Year)-1)},Month={Jan}>}LineSalesAmount),''))

so maybe the bits I've added an underlined = symbol below are missing an '=' just before the 'Only(Jahr)'

if(valuelist($(=Only(Jahr)),$(=Only(Jahr)-1))=$( = Only(Jahr)),count({$<Jahr={$(=Only(Jahr))},Monat={Jan}>}UmsatzBestZeile),

if(valuelist($(=Only(Jahr)),$(=Only(Jahr)-1))=$( = Only(Jahr)-1),count({$<Jahr={$(=Only(Jahr)-1)},Monat={Jan}>}UmsatzBestZeile),''))

It might be easier to create variables like vY and vLastY so you just have to do the dollar replacement in both dimension and expression e.g.

=if(valuelist($(vY),$(vLastY))=$(vY),sum({$<Year={$(vY)},Month={Jan}>}LineSalesAmount),

if(valuelist($(vY),$(vLastY))=$(vLastY),sum({$<Year={$(vLastY)},Month={Jan}>}LineSalesAmount),''))

Regards,