2 Replies Latest reply: Dec 20, 2011 12:23 PM by Vlad Gutkovsky RSS

    dimension using Valuelist with two equal values

      hi

      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
          Stefan Wühl

          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
            Vlad Gutkovsky

            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