topic Re: Re: chart sorting linked to clever expression in QlikView

chart sorting linked to clever expression

Fri, 08 Nov 2013 12:36:43 GMT

Hi,

SALTO reponse earlier in the week has enabled us to create charts per neighbourhood which is what we were looking for on an application; however, were now trying to get the charts to sort in order (i.e highest % down to lowest %). However, crucially, the legend colours need to remain consistent (e,g ASB - red). It does this (currently default of the chart) however the % are not in ascending order. When we change to sort by frequency it then upsets the legend colours so the colours are different on every chart.

It is important legend stays the same so we dont have to attach to each chart.

On aatatched refer to tab entitled - project time by neighbourhood

Please help.

Re: chart sorting linked to clever expression

Gysbert_Wassenaar — Fri, 08 Nov 2013 12:56:43 GMT

Click on the + in front of the expression on the Expression tab, select Background Color and enter in the Definition field this expression: color(FieldIndex('project title',only({1}[project title])))

Re: chart sorting linked to clever expression

Fri, 08 Nov 2013 13:46:34 GMT

Hi,

Thanks that has worked; however, this seems to be overriding the project title filter as it now shows out of office. On the main tab this project title has been asked to be excluded.

Cheers

Re: chart sorting linked to clever expression

Gysbert_Wassenaar — Fri, 08 Nov 2013 15:09:15 GMT

Your document was showing Out of office already in for example projects Brindley and Clifton Green.

Perhaps you want to change your expression to:

= Count({1<[project title]=p([project title]),[Neighbourhood]={'Brindley'}>} [Time (decimal hours)])

Count({1<[project title]=p([project title]),[Neighbourhood]={'Brindley'}>} TOTAL [Time (decimal hours)])

Re: chart sorting linked to clever expression

Mon, 11 Nov 2013 08:30:23 GMT

Hi Gysbert,

Thats seems to have worked.

In respect to the colour though it now only uses about 3, which are all variations of grey and blue.

I've added your expression onto 'Project Title - Agecroft' chart on the attached.

Can more colours be added that you get with a standard chart?

Re: chart sorting linked to clever expression

Gysbert_Wassenaar — Mon, 11 Nov 2013 08:56:30 GMT

On the Colors tab you can define 20 colors. If you need more colors you can create a table in the script as explained in this blog post: Colors in charts

Re: chart sorting linked to clever expression

Mon, 11 Nov 2013 09:09:31 GMT

Hi,

Thanks for this...have seen this article before. However, 20 colours is easily enough but the results aren't following the colours.

On the chart mentioned the results don't follow colours 1-6, 7-12 or 13-18. Colours 1-3 dont appear at all.

Chris

Re: chart sorting linked to clever expression

Gysbert_Wassenaar — Mon, 11 Nov 2013 09:16:29 GMT

Actually they do follow the colors, but the colors are assigned based on the order in which the project title values were loaded in the script. If you want a specific fixed order you need to first load the project titles in the order you want. Or you can use a color table like explained in the blog post.

Re: chart sorting linked to clever expression

Mon, 11 Nov 2013 15:38:46 GMT

Hi Gysbert,

Thanks very much..really trying to get to grips with this.

Read the post and developed subsquent table (see atatched excel sheet). However, can you advise on the script where and how you put in the ' 'mapping' instructions (step 2 and 3 on the blog)?

Sorry but would really appreciate the assistance.

Chris

Re: Re: chart sorting linked to clever expression

Gysbert_Wassenaar — Tue, 12 Nov 2013 16:50:40 GMT

A mapping table is created with a load statement preceded by the mapping keyword. The map can then be used in the applymap function.

MapColor:

mapping LOAD Activity, rgb(Red, Green, Blue) as ActivityColor

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD daydate,

[Time period],

....

[activity title],

applymap('MapColor',[activity title]) as ActivityColor,

....

FROM

(ooxml, embedded labels, table is [joined data]);