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



      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



      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...




      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