Tag: multiple data source

Can you create multi-select parameters?

Donna ColesLeave a comment

For this week’s #WOW2024 challenge, Kyle simulated a real-world challenge he’s faced at work where he wanted the ability to select multiple parameters as he was sourcing data from multiple data sources, so using traditional filters didn’t work.

Connecting to the data

The challenge required connecting to the Superstore data twice but applying a data source filter to each connection to restrict the Order Date to Year 2023 or 2024.

After making the 1st connection and filtering to 2023, I renamed the data source and appended 2023.

I then added a data source and made the 2nd connection to Superstore, this time adding a data source filter to 2024. I then renamed this data source and appended 2024. So I ended up with 2 data sources at the top of the data pane window.

Building the line charts

Starting with the Superstore – 2023 data source, put Order Date on columns and change to be the continuous (green) Week number option. Add Sales to Rows.

Then

  • format Sales to be $ with 0 dp
  • format Order Date to be mmm dd custom date format
  • to match the solution, set the Date Property of the data source to start a week on Sunday (right click data source > date properties)
  • Remove the titles against both axis
  • adjust the Tooltip
  • Remove all gridlines, zero lines, axis rulers & axis ticks
  • Name the sheet 2023 Sales

Create a new sheet, and the repeat all the steps but source the fields from the Superstore 2024 data source.

Building the Category Selection

On a new sheet, manually type MIN(1) into the Columns and add Category from the Superstore 2023 data source to Rows. Amend the axis to fix it from 0 to 1. Set to Entire View. Increase the Size to 100%. Add Category to Label. Adjust the font and align centrally. Hide the Category headers and the Min(1) axis. Remove the Tooltip.

To build the multi-select parameters, we’re going to use a sheet to capture the interactions the user makes into a parameter that will store a delimited string of selected values. This is using the same principles discussed in a previous challenge I created and blogged about here.

We need a parameter

pSelectedCategories

string parameter defaulted to empty string

We’ll use a parameter action to capture the user selection and add it into the pSelectedCategories parameter.

When using delimited strings, you need to choose a character to separate the field values that isn’t going to appear within the field value itself; ie choose a character that won’t appear within a Category name. In this instance I’m choosing | (pipe), and I’m going to build up the parameter as follows

ActionpSelectedCategoriesDisplay
Initial state<empty string>All Categories selected – coloured green
1 category selected eg Furniture|Furniture|Furniture is green, others categories grey
2 categories selected eg Furniture then Office Supplies|Furniture||Office Supplies|Furniture & Office Supplies are green, Technology is grey
3 categories selected eg Furniture, then Office Supplies, then Technology|Furniture||Office Supplies||Technology|All Categories selected – coloured green
Existing category is selected again eg Office Supplies|Furniture||Technology|Furniture & Technology are green, Office Supplies is grey

We need a calculated field to populate the parameter, which will get modified by comparing what’s already in the parameter with the Category being selected.

In the Superstore 2023 data source create

Category for Param

IF CONTAINS([pSelectedCategories], [Category]) THEN REPLACE([pSelectedCategories],’|’ + [Category] + ‘|’, ”) //selected category is already in the parameter, so remove it
ELSE [pSelectedCategories] + ‘|’ + [Category] + ‘|’ //append current category selected to the existing parameter string
END

Add Category for Param to the Detail shelf.

We need to set the colour of the bars. Show the pSelectedCategories parameter and manually type in |Furniture|

Then create

Category is Selected

[pSelectedCategories] = ” OR
CONTAINS([pSelectedCategories], [Category])

Add this to the colour shelf, and adjust the colours accordingly

Remove the text from the pSelectedCategories parameter, and all the bars should be green.

Format the bars so there is a light grey thick row divider, and set the background of the worksheet to the same light grey. Reduce the Size slightly, so there is a noticeable gap between the bars.

When added to the dashboard, we won’t want the unselected bars to ‘fade’, so we’ll use the True/False trick, which means we’ll need to create

True

TRUE

False

FALSE

and add both these fields to the Detail shelf.

Name the sheet Category.

Building the Region and Ship Mode Selections

Basically repeat the above steps on a separate sheet for each selector. You may find it easier to duplicate the Category sheet and then replace the various fields.

You’ll need to create a pSelectedRegions and a pSelectedShipModes parameter, and calculated Region for Param, Region Is Selected and Ship Mode for Param and ShipMode Is Selected calculated fields.

Name the new sheets Region and Ship Mode.

Filtering the line charts

On the 2023 Sales sheet, add Category Is Selected, Region Is Selected and Ship Mode Is Selected to the Filter shelf, and set all to be True.

Switch to the 2024 Sales sheet.

Recreate the 3 ‘Is Selected’ fields in the Superstore 2024 data source. You can either do this manually, or select the fields in the Superstore 2023 data source (ctl-click to multi-select), the right click and Copy

then switch to the 2024 Sales sheet, and right-click anywhere in the right hand data pane and paste.

Then add each of the fields to the filter shelf and set to True.

Adding the interactivity

Make sure all the parameters are empty, then add all the objects to a dashboard. I used a vertical layout container to place the Selector objects, as I could then set them to be distributed evenly. I also set the background of the layout container to the same light grey as the worksheet, and centrally aligned all the sheet titles.

6 dashboard actions are required, 2 for each selector.

Select Categories

Parameter action that on select of the Category sheet, sets the pSelectedCategories parameter with the value from the Category for Param field.

Deselect Categories

Filter dashboard action that on select of the Category sheet on the dashboard, targets the actual Category sheet, passing the values of True = False.

Create a version of each of these dashboard actions for the Region sheet and the Ship Mode sheet, and that should complete the challenge.

My published version is here.

Happy vizzin’!

Donna

Can you create a waffle chart?

Donna ColesLeave a comment

Kyle Yetter ended the #WOW2022 year with this challenge, to create a waffle chart. I haven’t built one for a while, so needed a quick google to refresh my memory.

You’ll need a ‘template’ data source which defines the grid pattern layout, and what each square represents. Every square represents a percentage point from 1% to 100%, and is positioned in a row numbered from 1 to 10 and a column numbered from 1 to 10. This information is stored in the ‘template’. You can build your own, or you can use the waffle_template.xlsx file I created that is stored here.

Connect to the Waffle_Template excel file and make Row and Column discrete dimensions (just drag them to above the line on the left hand data pane). Then add Row to Rows and Column to Columns and Percent to Text.

Building the waffle

Now connect to the Orders data from the SuperStoreSales data source. In that data source, create a new field

Consumer %

ROUND(SUM(IF [Segment]=’Consumer’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)

If the Segment is Consumer, get the sum of Sales as a proportion of the total Sales. Round the result to 2 decimal places.

Create similar fields for the other segments

Corporate %

ROUND(SUM(IF [Segment]=’Corporate’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)

Home Office %

ROUND(SUM(IF [Segment]=’Home Office’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)

Then in the Waffle_Template datasource, create the following field

Colour : Consumer

[Orders (Sample – Superstore)].[Consumer %]>=SUM([Percent])

The above calculation will return true when the % Sales for Consumer is greater than or equal to the Percent value.

Repeat and create equivalent fields for the other segments

Colour : Corporate

[Orders (Sample – Superstore)].[Corporate %]>=SUM([Percent])

Colour : Home Office

[Orders (Sample – Superstore)].[Home Office %]>=SUM([Percent])

Add Colour : Consumer to the Colour shelf, and adjust colours. If you get any warning messages about blending, just acknowledge.

To make the blocks, double click in the Columns shelf and type in MIN(1). Change the mark type to bar. Edit the MIN(1) axis, and fix it from 0 to 1. Remove the Percent field from the Text shelf.

Set the row and column dividers to be thick white lines, and hide the column and row headings and axis (uncheck Show Header). Uncheck Show Tooltip on the Tooltip shelf.

Name this sheet Consumer.

Then duplicate this sheet, and replace the Colour : Consumer pill on the Colour shelf with the Colour : Corporate pill. Adjust colours to suit. Name the sheet Corporate.

Then duplicate again, and repeat the process for the Colour : Home Office pill, naming the sheet Home Office.

Building the KPIs

On a new sheet, using the Orders – SuperStoreSales data source add Segment to Columns.

Create a new field

Percent of Total

ROUND(SUM([Sales]) / TOTAL(SUM([Sales])),2)

format this to percent with 0 dp and add this to the Text shelf.

Create a new field

Segment UPPER

UPPER([Segment])

and add this to the Text shelf.

Format the text and align centrally

Remove the row dividers, the Segment column labels, and uncheck Show Tooltips from the Tooltip shelf. Name the sheet BANs.

Creating the dashboard

Use a horizontal container to position the three waffle charts in. Ensure each chart is set to fit entire view. Hide the titles, and from the context menu on the container, distribute the contents evenly. Use padding on the objects to create more white space.

Add the BANs object underneath, remove the title and set to fit entire view. Adjust the heights and widths as required to ensure the waffles are spaced evenly and are square, and the BANs line up underneath.

My published viz is here.

Happy vizzin’!

Donna