Skip to Content

Chart.JPG

I’d like to share a mechanism I use to create the above table & graphic. For this example we are using a very simple E-Fashion query:

Query.JPG

The basis of this mechanism is using the FILL function to dynamically populate a cell with a symbol.

Firstly, once I know the size of the column I want to use, I use a FILL function to work out how many symbols it can hold:

=fill(“¦”;35)

The 35 is an arbitrary number. Experiment with it until you arrive at the number which your column can accommodate easily. I know my column holds 35 of these symbols.

Now, I need to work out which store has the greatest revenue amount:

=Max([Sales revenue]) ForAll ([Store name])

…which is e-Fashion Magnolia with $1,911,434.

If we divide this by our 35 number, we have an operator we can use on each stores Sales Revenue:

=Max([Sales revenue]) ForAll ([Store name])/35


If we now divide each stores Sales Revenue by this operator, we arrive at a Fraction of 35 – this is what we use in our FILL function. Add a ROUND function to make it neat:

=Fill(“¦”;Round([Sales revenue]/(Max([Sales revenue]) ForAll ([Store name])/35);0))


Finally, we apply an alerter to colour code the symbols based upon an arbitrary percentage of your choosing:

Alerter.JPG

…the [Max Revenue] variable is just:

=Max([Sales revenue]) ForAll ([Store name])

…and that’s pretty much it. Obviously you can use any symbol you like, and you can make your column any width you like and experiment with the capacity of it to derive the number to use with the FILL function.

You can easily modify the mechanism to work with static ranges or to use percentages instead. I hope you find this useful!

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply