4 Replies Latest reply: May 20, 2016 11:03 AM by Gysbert Wassenaar RSS

    Value List using column / row no - line chart help Qlik Sense

    Joanna Seldon

      Hi

       

      I have successfully created a Value List using column / row no - line chart line chart. This using the following:

       

      two dimensions

       

      Dimension 1 - CalendarYear

      Dimension 2- Departments ....has the expression .... ValueList('All', 'Catering', 'Children', 'Travel' )

       

      a Measure for %

       

      some of the expression as follows

       

      Pick(Rowno(),

      sum( {$<[DEPTSALEID] = {"1"}, DEPARTMENT=, CalendarYear = {">2013"}>}[VALUE])/sum ({$<[DEPTSALEID]= {"1"}, DEPARTMENT=,  CalendarYear = {">2013"}>}[EXVALUE]),
      if(index(GetFieldSelections([DEPARTMENT],'|',1),'Catering'),sum( {$<[DEPTSALEID] = {"1"}, DEPARTMENT={'Catering'}, CalendarYear = {">2013"}>}[VALUE])/sum ({$<[DEPTSALEID]= {"1"}, DEPARTMENT={'Catering'},  CalendarYear = {">2013"}>}[EXVALUE])),
      if(index(GetFieldSelections([DEPARTMENT],'|',2),'Children'),sum( {$<[DEPTSALEID] = {"1"}, DEPARTMENT={'Children'}, CalendarYear = {">2013"}>}[VALUE])/sum ({$<[DEPTSALEID]= {"1"}, DEPARTMENT={'Children'},  CalendarYear = {">2013"}>}[EXVALUE]))

       

       

      this works well as this only compares 3 or all departments. Also easy to set up the chart...

       

      however

       

      this issue I have is I have is for the departments dimension, I want to use a data column called Sub Department for Dimension 2 instead. This list is very long. contains over 100 values. Also thus making the Measure to end up going to be a very large expression.

       

      is there an easier way please

       

      Kind Regards