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

[joeybird]

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

[marcus_sommer]

You could use a $-sign expansion: The Magic of Dollar Expansions like:

$(=chr39) & concat(distinct [Sub Department], chr(39) & ',' & chr(39)) & chr(39))

to catch the dimension-values. These technique maybe combined with Variables with Parameters could shortened your expression. But I'm not sure if you really need these valuelist-stuff or if it's not easier to create this as a normal field within the datamodel.

- Marcus

[Gysbert_Wassenaar]

I think you should simply create a amall extra table in the script:

ReportDep:

LOAD DISTINCT

     DEPARTMENT as ReportDepartment,

     DEPARTMENT

FROM

     ...source_of_department...;

CONCATENTATE (ReportDep)

LOAD DISTINCT

     'All departments' as ReportDepartment,

     DEPARTMENT

FROM

     ...source_of_department...;

Then use the new ReportDepartment as the second dimension. If you want instead to use a column Sub Department then create the ReportDep table based on that field. This will work a lot better than a synthetic dimension and a huge pick( ... ) expression.

[joeybird]

Hiya

this works, but it shows all the lines..for each department.

hence why I was using a pick (...) list expression,

Please advise

[Gysbert_Wassenaar]

That sounds like you have two tables in your data model that are not associated. If true then that should be fixed. Rename the relevant fields in the tables so they are associated properly. If it's not the case then please post a small qlikview document that allows us to see what's going on.