For this week’s challenge I expanded on my challenge from week 13. So for this solution guide, I’ll be starting with the workbook I built for week 13 and adjusting as required. You can either build on your own solution if you took part, start with my workbook, or rebuild from scratch (my solution guide to week 13 is here).

Based on the hint I provided in the challenge, I’m going to build this with 3 sheets; one sheet to display the bar charts per year for the specific Sub-Category, then one sheet to display the segmented bars up to the selected Sub-Category and one sheet to display the segmented bars below the selected Sub-Category.

For this we need to assign an index to each row of data.

Sub-Cat Index

INDEX()

Make this discrete and add it to Rows in front of Sub-Category and adjust the table calculation so that it computes using Sub-Category only. Each row should be numbered from 1 – 17.

Creating the top & bottom bar chart

We will need to be able to identify the name of the Sub-Category selected and the associated index value. We’ll use parameters to store this information

pSelectedSubCat

string parameter defaulted to ” <empty string>

and

pIndex

integer parameter defaulted to a very large number, in this case I chose 10,000 (basically a number higher than the number of dimensions listed.

Show the parameters, and manually type into pSelectedSubCat the word ‘Copiers’ and into the pIndex type 8 (the index associated with Copiers).

We want to display an arrow indicator based on whether a Sub-Category is selected or not. We need

Arrow Indicator

IF [pSelectedSubCat] = [Sub-Category] THEN ‘▼’ ELSE ‘►’ END

Add this onto the Rows after the Sub-Category pill. Readjust the table calculation of the Sub-Cat Index so it is computing by Arrow Indicator as well.

We only want to show up to the selected Sub-Category

Show Top

[Sub-Cat Index]<=[pIndex]

Add this to the Filter shelf and set to True. Verify the table calculation is also computing by Sub-Category and Arrow Indicator only. Hide the Sub-Cat Index column and format the arrows so they are coloured brown and aligned top centre. Name this sheet Top or similar

Duplicate the sheet.

To show the bottom half of the chart create

Show Bottom

[Sub-Cat Index]>[pIndex]

Remove Show Top from Filter and add Show Bottom instead. Set to True. Again verify the table calculation is computing by Sub-Category and Arrow Indicator only. Call this sheet Bottom or similar.

Building the year bar chart

On a new sheet add Order Date to Rows and Sales to Columns. Change mark type to bar. Adjust Colour and size

Create a new field

Is Selected Sub-Cat

[Sub-Category] = [pSelectedSubCat]

Add to Filter and set to True.

Add another copy of Sales to Columns, then double click into the pill, and manually wrap the text with WINDOW_MAX(….. )

On the Window_Max marsk card, reduce the size to as small as possible and add a white border (via the Colour shelf). Add Sales to the Label shelf and adjust the font to be smaller and coloured brown.

Make the chart dual axis and synchronise the axis. Remove measure Names from the All marks card, and right click on the top axis and move marks to back. Hide both axes, remove row/column dividers and gridlines/zero lines. Adjust the font colour and size of the row labels, and remove the row header.

Add Sales to the Tooltip shelf of the All marks card, and add a quick table calculation of percent of total. Format to 1 dp. Add Sub-Category to the Tooltip shelf too, and then adjust the Tooltip of the All marks card to suit. Name the sheet Years or similar.

Building the dashboard

On the existing dashboard, add a vertical container, and then add the Top, Years and Bottom charts within it.

Remove the chart titles.

You’ll notice that the bar for bookcases in the bottom chart is longer than the bar for copiers in the top chart. We don’t want this. To correct we need

Max Sales Axis

{MAX({FIXED [Sub-Category]:SUM([Sales])})}

Navigate to the Top sheet and add Max Sales Axis to the Detail shelf of the All marks card

Create a new parameter

pMaxAxis

float parameter defaulted to 500,000

On the Bottom sheet, show the Sales axis, then edit the axis and adjust so the axis range is Custom from 0 to pMaxAxis

Hide the Sales axis again

Adding the Interactivity

We will use parameter actions to set the various parameters on click, but we need to ensure the viz will ‘expand’ but also fully ‘collapse’. So we need

Sub-Cat to Pass

IF [Is Selected Sub-Cat] THEN ” ELSE [Sub-Category] END

Index to Pass

IF MIN([Is Selected Sub-Cat]) THEN 10000 ELSE [Sub-Cat Index] END

ie if we’re selecting the Sub-Category that’s already been selected then we need to ‘reset’ back to the start state.

Add both of these fields to the Detail shelf of the All marks card on both the Top and Bottom sheets.

Change Sub-Cat to Pass to be an Attribute so that it doesn’t impact the existing table calc settings, and verify Index to Pass is computing by Sub-Category and Arrow Indicator.

Back to the dashboard, and add the following dashboard action

Select SubCategory

On select of the Top or Bottom sheets, set the pSelectedSubCat parameter, passing in the value from the Sub-Cat To Pass field. When clearing the selection, reset to ” <empty string>

Set Index

On select of the Top or Bottom sheets, set the pIndex parameter, passing in the value from the Index To Pass field aggregated at the minimum level. When clearing the selection, reset to 10,000

Set Axis

On select of the Top sheet, set the pMaxAxis parameter passing in the value from the Max Sales for Axis field. When cleared reset to 50,000

You should now have a functioning chart. You may need to adjust padding of the objects and modify width of headers on the charts to get the alignment as required. Update the chart title as required as well.

My published viz is here.

Happy vizzin’!

Donna