Can you show missing selected periods and autosize bars?

Newly wed Lorna Brown (previously known as Lorna Eden 😉 ) returned for this week’s #WOW challenge.

The requirement was to create a bar chart showing number of orders per subcategory per day/week/month over a user defined number of years. The chart is coloured based on Profit. The bars also need to change size based on what date part was being displayed Lorna already hinted they’d be parameters and set actions involved.

Building the chart

First off let’s create the parameters we need to drive the dates we need to include in the viz.

Select Period

A string parameter listing Daily, Weekly, Monthly. The trick here is to store the datepart of day, week, month as the value, while displaying the required text for selection. Default to Weekly.

Doing this means later we can refer directly to the parameter when we need some date manipulation.

Number of Years

A range integer parameter starting from 1 to 4, defaulting to 2

With these, we can define the Date field that we need to plot on our axis

Date to Plot

DATE(DATETRUNC([Select Period],[Order Date],’Sunday’))

This has the effect of truncating every order date to the 1st day of the relevant month or week or just to the day, so for example if ‘month’ is selected all the orders placed in May 2019 will be grouped together under 01 May 2019 etc.

Dates to Include

YEAR([Order Date])>={MAX(Year([Order Date]))}-([Number of Years]-1)

{MAX(Year([Order Date]))} is a shortened notation for the level of detail calculation (LoD)

{FIXED : MAX(Year([Order Date]))}

which basically returns the highest year in the data set, which in this case is 2019. If the [Number of Years] parameter is set to 1 for example, we would expect all of 2019 to display, hence we need to subtract 1 in the formula so we get all orders in 2019.

Adding this to the Filter shelf and set to true will limit the orders to the dates in the years required.

Finally we need the measure

Number of Orders

COUNTD([Order ID])

Right, with those parameters set, we can now build the basic bar chart. For now we’ll just restrict the data to a single Sub-Category directly – we’ll look to adjust this later.

  • Add Dates to Include = True to Filter
  • Add Sub-Category = Tables to Filter
  • Add Date to Plot as a continuous exact date to Rows (green pill)
  • Add Number of Orders to Columns
  • Add Profit to Colour
  • Set Mark Type to Bar
  • Adjust the Tooltip to match
  • Show the Select Period & Number of Years parameters
  • Adjust the Sheet Title to reference the Select Period parameter

Sizing the bars

You’ll see the bars all look a bit overlapped. You might be tempted to adjust the bar size by moving the slider to reduce the overlap, which may well work, but as you change the Select Period parameter you’ll find that what you’re doing is setting the bar width to set width that is the same whether you have 24 marks displayed (monthly for 2 years) or 156 marks (daily for 2 years). The requirement is for the bar to adjust in width, so it’s wider when there’s less marks.

To do this, we need a calculated field

Size

CASE [Select Period]
WHEN ‘day’ THEN 1
WHEN ‘week’ THEN 5
WHEN ‘month’ THEN 10
END

These are just arbitrary values I chose, and you can play around with the values to suit, but the key is you’re choosing a range of numbers with the smallest for ‘day’ and the largest for ‘month’.

Add this field to the Size shelf. and change it to be a Continuous Dimension ie a green pill of just Size rather than SUM([Size]).

Then click on the Size shelf and change from Manual to Fixed, and set the alignment to Centre.

Changing the Select Period parameter you’ll see the bars adjust their width from being very narrow for Daily, wider for Weekly, and wider again for Monthly.

Sub Category Selector

This is based on techniques that have cropped up in a few #WOWs this year.

We’re going to be using Set Actions for this bit, so to start we need a Set.

Right click on Sub-Category -> Create Set. Name the set Selected Sub-Category and just select a single value, Tables for now.

Now on a new sheet, add MIN(0.0) to Columns (type directly in) and Sub-Category to Rows.

  • Change the Mark Type to Shape
  • Add Sub-Category to Label
  • Add Selected Sub Category set to Shape, and adjust the shape and colour to suit
  • Create a calculated field called True and another called False, each containing the value True and False respectively – add these to the Detail shelf.
  • Edit the Axis to be Fixed to start at -0.07 to 1. This is to shift everything to the left.
  • Turn off Tooltips
  • Format to remove all column, row, zero & grid lines & axis rulers.
  • Uncheck Show Header on Sub-Category and MIN(0.0)

Building the dashboard

First up, now we’ve got a Set to store the Selected Sub-Category, remove, the Sub-Category field from the Filter shelf of the bar chart. Add the Selected Sub-Category set to the Filter shelf instead.

Now create a new dashboard sheet and add the bar chart to it.

By default, the Profit colour legend and parameters will be displayed in a vertical layout container to the right of the bar chart.

Remove the colour legend, and position the Select Period parameter above the Number of Years.

Then add the Selector sheet between the two parameters, and remove any additional legends that get added.

Add a dashboard action Deselect against the Selector sheet to stop the un-selected Sub-Categories from fading out.

Add a further dashboard action Select Sub Cat against the Selector sheet to set the value of the Selected Sub-Category set on selection. The dashboard action should be set flagged to Run on single select only, so multiple values can’t be chosen.

Change the vertical layout container to be floating, then adjust the height and set the background colour to white.

Use the Add Show/Hide Button option on the Layout container to enable the collapsible container functionality.

You’ll just need to move things around a bit, adjust the sizes to suit, but that should be pretty much it.

My published version is here.

Keep safe & Happy vizzin’!

Donna

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s