Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
Hiya
this works, but it shows all the lines..for each department.
hence why I was using a pick (...) list expression,
Please advise
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.