chart sorting linked to clever expression

[#1]

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.

[Gysbert_Wassenaar]

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])))

[#3]

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

[Gysbert_Wassenaar]

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)])

[#5]

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?

[Gysbert_Wassenaar]

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

[#7]

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

[Gysbert_Wassenaar]

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.

[#9]

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

[Gysbert_Wassenaar]

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]);