Ann Jackson returned with this week’s #WOW2020 challenge, to create a ‘clustered histogram’, whereby the orders in 2020 were placed in ‘bins’ based on the sale value.

I found this challenge quite straightforward this week, as its very similar to a previous challenge I’ve already blogged about in week 23, which created a side by side bar chart by month.

So what are the main points for this blog

• Creating the bins
• Getting the bars side by side
• Tooltips

Creating the bins

I first created an LoD to store the total value of the sale for an Order

Order Value

{FIXED [Order ID]: SUM([Sales])}

then I need to ‘bin’ this, but as the sales over $2000 needs to be lumped together, we can’t use the traditional binning functionality. We also don’t want to have a massive case statement to assign the values. Instead we can do a bit of maths…. we want to essentially round each order value to the nearest 100.

Round Up to 100

(CEILING([Order Value]/100) * 100)

So if the Order Value is 39 for example, when divided by 100 this will be 0.39. The CEILING function always rounds up to the nearest whole number, so in this case will return 1, which is then multipled by 100 to give us 100.

Doing this, every order value is then assigned a ‘bin’ of 100, 200, 300 etc

I then created

Sales Bin

IF [Round Up to 100] >2100 THEN 2100 ELSE [Round Up to 100] END

to apply the grouping of all the values which were greater than 2100 (since 1999 would be rounded up to 2000 and 2001 would be rounded up to 2100).

Getting the bars side by side

We have the Sales Bin measure and will also need to plot the count of orders

# Orders

COUNTD([Order ID])

Plotting this out and splitting by Segment we get

but we don’t want the segments stacked, we want them side by side.

The Sales Bin axis is ‘continuous’, which means a value can be plotted at any number along the line, it just happens to be at the ‘100’ marks as that’s where are bins are.

So we use a sort of ‘jittering’ to plot each bar at a slightly different value depending on the segment

SALE AMOUNT

CASE [Segment]
WHEN ‘Consumer’ THEN [Sales Bin] – 75
WHEN ‘Corporate’ THEN [Sales Bin]- 50
ELSE [Sales Bin]- 25
END

So for all the orders in the ‘100’ bin (ie the order value was between 1 and 100), all the Consumer orders will actually get plotted at 25, Corporate at 50 and Home Office at 75.

All this is explained in much more detail in the Week 23 blog post referenced at the top of this post.

Tooltips

The tooltip has different text depending on where you hover.

We need the lower value for the bin range (eg $0-100$), so I created

Round Up to 100 minus 100

[Round Up to 100]-100

And I then created 4 different ‘tooltip’ calculations which I could place on the tooltip to give me the display I needed:

TOOLTIP Upper

IF [SALE AMOUNT]<2000 THEN [Round Up to 100] END

TOOLTIP Lower

IF [SALE AMOUNT]<2000 THEN [Round Up to 100 minus 100] END

TOOLTIP between

IF [SALE AMOUNT]< 2000 THEN ‘ between’ END

TOOLTIP Symbol

IF [SALE AMOUNT]< 2000 THEN ‘ – ‘ ELSE ‘$2000+’ END

The final thing needed is to create a reference band to colour the section at the end, and fix the axis to start after 0 and end at 2099, so you don’t get 0 and 2100 displayed on the axis.

So a relatively short write up today – think this is the quickest blog I’ve written.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna