Tag: Phone Layout

Can you build a mobile KPI app?

Donna Coles1 Comment

For week 24 of #WOW2023, Kyle set this challenge involving dynamic zone visibility to build a mobile friendly KPI visual.

The charts being displayed are relatively simple, and use techniques applied several times in other challenges. Let’s tackle the charts for each ‘page’ one by one.

The Home page

For the Sales KPI

  • Double click into Columns and type MIN(1)
  • Add Sales to Label.
  • Set to Entire View
  • Adjust the MIN(1) axis to be fixed from 0-1
  • Adjust the text in the Label dialog to include the word Sales and resize fonts.
  • Align the label middle centre
  • Increase the size to the maximum value
  • Adjust the colour
  • Format the Sales measure to be $ with 0 dp.
  • Uncheck the show tooltip option from the Tooltip dialog
  • Hide the Min(1) axis
  • Ensure no row/column divider lines are displayed and remove all gridlines, zero lines, axis ticks etc

Duplicate this sheet and create equivalent ones for Profit and Orders – you’ll need to create a field

Count Orders

COUNTD([Order ID])

for the orders KPI.

For the Sales Sparkline

  • Add Sales to Rows
  • Add Order Date as a continuous (green) pill set at the Month level.
  • Adjust the colour
  • Hide both the axis
  • Remove all gridlines, zero lines, axis lines, row/column dividers
  • Adjust the tooltip
  • Format the background of the worksheet to light grey (#f5f5f5).

Duplicate the sheet and create equivalent ones for Profit and Orders.

You should have 6 sheets for the home page.

The Category Page

The Category page is displaying values for the last 12 months based on ‘today’. If building this in a business environment, I would make use of the TODAY() function. But to ensure the viz doesn’t break in future, I’ll hardcode today within a parameter

pToday

date parameter defaulted to 14 June 2023

I then need a field to restrict the records to report over

Last 12 months only

[Order Date]>= DATEADD(‘month’, -12, DATETRUNC(‘month’,[pToday])) AND
[Order Date]<DATEADD(‘month’, 1,DATETRUNC(‘month’,[pToday]))

this will return true if the Order Date associated to the record is greater than or equal to the 1st of the month, 12 months ago, based on the pToday parameter, and the Order Date is less that the 1st of next month, based on pToday.

For the Category KPI sheet

  • Add Last 12 months only to the filter shelf and set to true
  • Add Category to Rows
  • Double click on Columns and type in MIN(1)
  • Add Sales and Category to Label
  • Set to Entire View
  • Adjust the MIN(1) axis to be fixed from 0-1
  • Adjust the text in the Label dialog and resize fonts.
  • Align the label middle centre
  • Increase the size to the maximum value
  • Adjust the colour
  • Uncheck the show tooltip option from the Tooltip dialog
  • Hide the Min(1) axis
  • Hide the Category column
  • Ensure no row/column divider lines are displayed and remove all gridlines, zero lines, axis ticks etc

For the Category trend

  • Add 12 months only to filter and set to true.
  • Add Category and Sales to Rows
  • Add Order Date as a continuous (green) pill set at the Month level.
  • Adjust the colour
  • Add Sales to Label and show the min & max labels for each Category pane
  • Hide both the axis
  • Remove all gridlines, zero lines, axis lines, column dividers
  • Adjust the tooltip
  • Format the background of the worksheet to light grey (#f5f5f5).

The Segment Page

Repeat the same steps as described above for the Category Page, but replace any reference to Category with Segment.

Sales by State Page

  • Add State/Province to Rows
  • Add Sales to Columns
  • Sort by Sales descending
  • Adjust the Colour
  • Click on the Label shelf and Show mark labels
  • Remove all gridlines, zero lines, axis lines, row/column dividers
  • Adjust the tooltip
  • Format the background of the worksheet to light grey (#f5f5f5).

Building the navigator

I went down a slightly longwinded route for this, but its still an acceptable method. I knew deep down it could be done in 1 sheet, but my brain just wasn’t quite wired up properly when I built it.

I basically ended up building 2 sheets per symbol.

Firstly, you’ll need to add the symbol images into your shapes palette.

Create a new field

Selection – Home

“Home”

Also create fields

True

TRUE

and

False

FALSE

  • Add Selection – Home to the Text shelf.
  • Change the mark type to Shape and select the ‘home’ shape from your custom shape palette.
  • Set to Entire View, then adjust the Label alignment to be bottom centre.
  • Uncheck the show tooltip option from the Tooltip dialog
  • Format the background of the worksheet to medium grey
  • Add True and False to the Detail shelf.
  • Name the sheet Home – Unselected or similar

Duplicate sheet and change the background colour to teal or similar. Name this sheet Home – Selected or similar.

Repeat the process building 2 sheets for each image – you’ll need to create a Selection– Category field, a Selection – Segment field and a Selection – State field.

Building the calcs for Dynamic Zone Visibility

In order to hide and show various content ‘on click’ we will be making use of dynamic zone visibility. For this we need several boolean fields created along with a parameter

pSelection

string parameter, defaulted to Home

We then need

Is Home Selected

[pSelection] =’Home’

Is Home Not Selected

[pSelection] <>’Home’

Is Category Selected

[pSelection] =’Category’

Is Category Not Selected

[pSelection] <>’Category’

Is Segement Selected

[pSelection] =’Segment’

Is Segement Not Selected

[pSelection] <>’Segment’

Is State Selected

[pSelection] =’State’

Is State Not Selected

[pSelection] <>’State’

Building the Dashboard

We need to make use of multiple (nested) containers in order to get all the content positioned in the right place. I’m not going to go through step by step which containers to place where, but just summarise the key points.

For the ‘navigator’ strip, all 8 sheets need to be placed side by side in a horizontal container, and should be ordered so the ‘home’ sheets are first, then the ‘category’ ones etc. I adjusted the padding around each object to be 1px, and obviously didn’t show the title.

For each sheet, determine whether it should display or not by using the control visibility using value option on the layout tab, and selecting the appropriate field based on which ‘page’ the sheet relates to , and whether it’s the ‘active’ / selected sheet or not.

Eg for the teal Home – Selected sheet, the control visibility using value option should be driven based on the value of the Is Home Selected field, while the grey Home – Unselected sheet should be based on the value of the Is Home Not Selected field.

If all these are set correctly, only 4 of the 8 sheets should be visible at any one time – 1 teal and 3 grey.

For the ‘pages’ ie the set of sheets visible based on the selection in the navigator, a Horizontal Container should be used which in turn consists of 1 vertical container (for the sheets relating to the Home page), 2 horizontal containers (1 containing the 2 sheets side by side for the Category page, and 1 containing the 2 sheets side by side for the Segment page), and finally the Sales by State sheet should be added to the main horizontal container.

The Sales by State sheet should be visible based on the Is State Selected field. Each of the other containers should be visible based on their relevant field.

When putting all this together, the dashboard might look crowded and disorganised, but once the settings have been applied, only 1 page’ should be visible and then you can tweak padding and positioning if need be.

Capturing the selection

We need parameter actions to determine which card should display

Select Home

This parameter action should be applied when the Home – Selected or Home- Unselected sheets are clicked on, and it should set the pSelection parameter, passing in the Selection – Home field.

Equivalent parameter actions should then be created for each of the other Selected/Unselected sheets, passing in the appropriate Selection – xxx field.

Finally to ensure the navigation options don’t remain ‘selected’ on click (the images look darker) we need to apply filter actions to set the true field to false on each of the navigation buttons – this means 8 filter actions, which should look similar to this…

The source sheet selected on the dashboard should target the actual sheet itself (not the one on the dashboard).

Add a title and any other content onto the dashboard. Finally to ensure the viz works properly on a mobile, delete the phone layout option that is automatically listed on the dashboard tab.

My published instance is here. Check out Kyle’s solution to see the 1-sheet navigator.

Happy vizzin’!

Donna

Designing KPIs for Mobile

Donna ColesLeave a comment

Luke Stanke returned for week 43 of #WOW2020 with a challenge focussed on building KPIs for mobile consumption.

In general this looked to be (and was) less taxing than some from previous weeks, but Luke did throw in some very specific requirements which did prove to be a bit tricksy.

To deliver this solution I built 8 sheets, 1 for each KPI heading and 1 for each bar chart. The dashboard then uses a vertical layout container to arrange the 8 objects in. A filter control on each bar chart determines whether the bar chart should ‘show’ or not. When a particular bar chart is displayed it fills up the space, which makes the display look to ‘expand’. Parameter actions are used to drive the ‘expand/collapse’ functionality.

The areas of focus for this blog are

  • Building the KPI chart
  • Formatting the Bar chart
  • Expand / Collapse function
  • Ensuring the KPI isn’t highlighted on selection
  • Making the display work for mobile

Building the KPI Chart

Because we have text on the left and the right, then I built this as a dual axis chart.

I’m going to build the Sales KPI.

I used MIN(1) on Columns, with Mark Type of bar, and fixed the axis to range from 0 to 1. SUM(Sales) is then added to Label, right aligned and formatted appropriately.

For the 2nd axis, we’re going to use MIN(0) positioned alongside MIN(1) on Columns, and this time, set the Mark Type to Gantt. I type the word ‘SALES’ into the Label field.

We also need to display a + or – icon on this label too. This will rely on a parameter that is going to be set.

Create a string parameter Selected Measure which is just empty.

For the icon, then create

Sales – Icon

IF [Selected Measure] = ‘SALES’ THEN ‘â–¬’ ELSE ‘✚’ END

I use this site to get the characters I use for these types of things.

Add Sales – Icon to the Label shelf of the MIN(0) axis, and position in front of the ‘SALES’ text.

If you show the Selected Measure parameter on the screen, and enter the word SALES, you’ll see the shape change to ‘-‘. Make this a dual axis chart and synchronise axis, and you should have the basis of the KPI (once all the axis have been hidden of course).

We also need to add an additional field onto the chart that we’ll need to use with the parameter action later.

Sales – String to pass

IF [Selected Measure] <> ‘SALES’ THEN ‘SALES’ ELSE ” END

When the Selected Measure parameter is empty (or contains another value), then this field will contain ‘SALES’ otherwise it’ll be blank.

Add this onto the Detail shelf of the ‘All’ Marks card (ie it needs to exist on both axes).

You essentially need to duplicate this sheet and build instances of the calculated fields for for Profit, Margin (SUM(Profit)/SUM(Sales)), and Customers (COUNTD(Customer ID)).

Formatting the Bar chart

Luke is very specific in his requirements that the axis tick for the bars displayed needs to be centred. This means there needs to be a continuous (green) pill on the date axis.

This very tiny requirement gave me a lot of grief 😦

I initially used MONTH(Order Date) (which will return numbers 1-12), but when I formatted the axis to First Letter, I get a D (for 0) and J (for 13) at each end.

Fixing the axis to start from 1-12 doesn’t work, as this chops off part of the first and last bars.

I tried a variety of mechanisms but to no avail. A chat with my fellow #WOW participant Rosario Gauna, who was also mulling over the issue eventually provided a solution, courtesy of Annabelle Rincon.

We need to use an ‘undocumented’ formatting option of mmmmm, which is the formatting for first letter. However, this doesn’t work with the above. I need to change the axis to use an actual date field (which was one of the mechanisms I’d already tried).

Baseline Date

DATETRUNC(‘month’,MAKEDATE(2019,MONTH([Order Date]), DAY([Order Date])))

This is taking every date in the data set, and ‘baselining’ it to all be on the same year (I chose 2019, but this could be any year you choose). The DATETRUNC is then setting all dates within the same month to be reported as being the 1st of the month.

Adding this to Columns instead as an exact date, and then formatting the axis and changing the Dates option to mmmmm will display the 1st letter

There is also a requirement to just show a label and tick mark for every other month, starting in Feb. You need to edit the axis, and on the Tick Marks tab

  • Set Major Ticked Marks to Fixed
  • Set the Tick Origin to 01 Dec 2018, and the interval to every 2 months (2 months after 1st Dec 2018 is 1st Feb 2019, so the F displays while the J for Jan doesn’t)
  • Set Minor Tick Marks to None

An additional ‘formatting’ setting I used on the bar charts was to fix the vertical axis to a number high enough to ensure the label always displayed after the bar and not in it. What this needs to be set to will vary and is only really noticeable once added to the dashboard, so may require some tweaking to get it correct.

Once again, you’ll need an instance of this bar chart for each of the measures Sales, Profit, Margin & Customers.

Expand / Collapse Function

On the dashboard, you need to use a vertical layout container, and place all the sheets in order underneath each other, so you have KPI, bar, KPI, bar etc.

Each bar chart needs a filter which will determine whether it shows any data or not. Create a calculated field

FILTER : Selected Measure

[Selected Measure]

On the Sales bar chart, make sure the Selected Measure parameter contains the value ‘SALES’, then add the Filter: Selected Measure to the Filter shelf and select the value SALES (it’ll be the only option available)

Then go to the Profit bar chart, and change the Selected Measure parameter to PROFIT. Now add the FILTER : Selected Measure to the Filter shelf and select the value PROFIT. If you switch back to the Sales bar, you’ll find the display is empty. Repeat this for the MARGIN and CUSTOMERS bar charts.

If you return to the dashboard you should find you probably have the 4 KPIs displayed and 1 bar chart showing. The final step is to add parameter actions.

For each KPI sheet, create a parameter action that targets the Selected Measure parameter by passing the relevant <Measure> – String to Pass field.

Ensuring the KPI isn’t highlighted on selection

I’ve used the True = False concept which I’ve applied multiple times to previous challenges.

Create a calculated field called True that just stores the value of True and and a calculated field called False that stores the value False. Add both of these to the Detail shelf of teach KPI viz.

Then on the dashboard, add a Filter action for each KPI sheet that goes from the KPI object on the dashboard to the sheet itself, passing the values Source: True = Target: False

Making the display work for mobile

This frustrated me no end. When I click on the Phone layout option (right image below), the layout container just doesn’t behave as it does on the Default view – the space for the bar charts is retained even when there’s no data.

I tried all sorts of combinations of containers to try to resolve this, and just couldn’t get it, and I struggled to find anything online that would help. I published to Tableau Public to test what the result would actually look like on my mobile, in the vain hope it might ‘just work’ but it didn’t 😦

When Sam Epley posted his solution, and I found it worked on mobile, I had to look. and found that you could just ‘delete’ the phone layout.

A couple of gems in this one that I need to store away for future! My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you calculate weekday run rate?

Donna ColesLeave a comment

This week’s #WoW2020 challenge was set by Meera Umasankar who once again was tackling the concept of ‘run rate’, but this time with an added twist – only consider the working days (ie the typical Mon-Fri weekdays), rather than every day in the month, the assumption being, this ‘business’ does not trade on weekends. Following on from last week’s challenge, Meera also chose to include a bit of blending to combine actual orders against the plan/target. For this Meera provided a custom dataset which just included a sheet of Actuals by Region for each day in April 2020 up to 23rd April, and Plan by Region for the whole month of April.

As per usual I started by putting together a table of data with the core numbers I was going to need per region : MTD value, Run Rate value, Plan value.

Building the key data fields

Whilst Meera had provided data just for April up to April 23rd, I decided to build this in a way as if the data could change.

Today

{FIXED: MAX([Date])}

This stores the maximum date from the Actuals data source – ie 23 April 2020.

Current Month Only

[Date]>=DATETRUNC(‘month’, [Today]) AND [Date] <= [Today]

When true, this will just consider the records in the Actuals data source that are dated between 1st April & 23 April. As it happens, due to the data provided, this will be everything, but in a typical business situation, you’re actuals would probably contain previous months data too.

MTD

IF [Current Month Only] THEN [Sales] END

Only stores the sales for the month we want to report on.

To get the plan we need to blend to the Plan data source. As the data in the Actual data source is per day, and the Plan is per month, we need to blend the data at the month level. Whilst this can be set in other ways, I like to be explicit when using blending, so in my Actuals data source I created

BLEND – Date

DATE(DATETRUNC(‘month’,[Date]))

This stores the 1st day of the month (1st April 2020) against every row of data.

In the Plan data I created a similar field, which is just essentially a duplicate field of the existing Date field, but by having the same name, it allows the blend joins to be automatically picked up.

Blend – Date

[Date]

Ok, let’s get these 2 measures on a table, to sense check we have the right figures so far :

  • Add Region to Rows (from the Actuals data)
  • Add MTD to Text
  • Add Plan to Text (from the Plan data)
  • Ensure the blend join links on both Region and BLEND – Date are clicked (due to the minimal data we have, the blend on Region only will work, but it’s good practice to include the date blend too if the Plan data contained different months).
  • Apply formatting as required to the MTD & Plan numbers

Calculating the Run Rate

Meera defines the Run Rate as being the value of Sales expected to be received in the whole month (the end of month position/forecast), based on the rate of sales so far in the month. So we’re looking to work out average sales made per day, then extrapolate that across the number of days in the month.

However, the twist in this challenge, is to only give consideration to the number of weekdays (ie working days).

As with many things, I chose to use my best friend ‘Google’ to see if it would throw up anything that may help this requirement, and it did, very quickly. There is an existing Tableau KB article that describes exactly how to work out the number of weekdays between 2 dates. You can find it here.

To work out the Run Rate I need

  • to work out the average Sales per weekday so far
  • multiply that by number of weekdays in the month

So I need to work out the number of working days between 1st April and 23rd April, and also the number of working days between 1st April & 30th April. I need a fair few calculated fields for all this, which I’ll build up rather than combine altogether.

Start of Current Month

DATETRUNC(‘month’,[Today])

simply truncates to 1st of month.

End of Current Month

DATEADD(‘day’, -1, DATEADD(‘month’, 1, [Start of Current Month]))

Adds 1 month onto start of month, then takes off 1 day to get the last day in the month

Following the steps in the article, I need to adjust these dates if they happen to fall on a weekend.

Start of Current Month (shift to weekday)

IF DATEPART(‘weekday’, [Start of Current Month]) = 1
THEN DATEADD(‘day’, 1, [Start of Current Month])
ELSEIF DATEPART(‘weekday’, [Start of Current Month]) = 7
THEN DATEADD(‘day’, 2, [Start of Current Month])
ELSE [Start of Current Month]
END

If the Start of Current Month lands on a Saturday or a Sunday, the start is shifted forward to the following Monday.

End of Current Month (shift to weekday)

IF DATEPART(‘weekday’, [End of Current Month]) = 1
THEN DATEADD(‘day’, -2, [End of Current Month])
ELSEIF DATEPART(‘weekday’, [End of Current Month]) = 7
THEN DATEADD(‘day’, -1, [End of Current Month])
ELSE [End of Current Month]
END

If the End of Current Month lands on a Saturday or Sunday, the end is shifted back to the previous Friday.

#Weekdays in Month

MIN(
(DATEDIFF(‘day’, [Start of Current Month (shift to weekday)], [End of Current Month (shift to weekday)]) + 1) – (2 * DATEDIFF(‘week’, [Start of Current Month (shift to weekday)], [End of Current Month (shift to weekday)])))

This is working out the number of days between the adjusted start & end dates, then adding 1 to this number. It then works out the number of weeks between the adjusted start & end dates, multiples by 2 (since in every week there are 2 weekend days), and then this number is subtracted from the first.

We then need to repeat this to work out the working days from start to today.

Today (shift to weekday)

IF DATEPART(‘weekday’, [Today]) = 1
THEN DATEADD(‘day’, -2, [Today])
ELSEIF DATEPART(‘weekday’, [Today]) = 7
THEN DATEADD(‘day’, -1, [Today])
ELSE [Today]
END

This is our end date, so the date is once again shifted back to the previous Friday if it happens to be a Saturday or Sunday.

# Weekdays from start to Today

MIN(
(DATEDIFF(‘day’, [Start of Current Month (shift to weekday)], [Today (shift to weekday)])+ 1) – (2 * DATEDIFF(‘week’, [Start of Current Month (shift to weekday)], [Today (shift to weekday)])))

Now we have these values, we can work out

Run Rate

(SUM([MTD])/[# Weekdays from Start to Today]) * [# Weekdays in Month]

Format this and add to check table

Building the chart

The left hand side of the chart is all text, but to present it as required, we need a fake axis.

  • From the Actuals data source, add Region to Rows
  • In the Columns shelf type in MIN(0) to create the fake axis.
  • Change the Mark Type to Text
  • Add Region, MTD, Run Rate from the Actuals data source to Text shelf
  • Add Plan from the Plan data source to the Text shelf (don’t forget to check the blend links)
  • Make each row bigger if everything all seems a bit squashed.

We’ll come back to formatting these fields later. Let’s now get the bar displayed & target displayed. This is a dual axis combining a bar and a gantt chart. Add to the chart as follows

  • Add Run Rate to Columns
  • Change the Mark Type of this measure to Bar
  • Remove all fields apart from Run Rate from the Label shelf of this card
  • Change Alignment of the Label to be left aligned
  • Add Plan to Columns (check the blend links)
  • Change Mark Type to be Gantt and remove fields from Label shelf of this card
  • Set to Dual Axis and Synchronise Axis.
  • Remove Measure Names from Colour shelf of the Bar and Gantt marks cards
  • Change Colour of the Gantt Bar (Plan) to black and add a black border to make it a bit thicker
  • Turn off Tooltips on All marks cards.

Indicating if Plan isn’t going to be met

The bar chart should be red if the Run Rate is less than Plan. The Run Rate on the Text side should also be displayed in red too if it doesn’t meet and black otherwise. We’re going to need some additional fields for this.

Run Rate < Plan

[Run Rate] < SUM([Plan (2020_04_22_WW17_Sales Projection)].[Plan])

Add this to the Colour shelf on the Bar marks card, and adjust the True/False colours accordingly

We can’t conditionally format an individual field in a Text display, so we need to create 2 further instances of the Run Rate field, where only one will ever display.

Run Rate < Plan (red)

If [Run Rate < Plan] THEN [Run Rate] END

Run Rate > Plan (black)

If NOT([Run Rate < Plan]) THEN [Run Rate] END

Format these accordingly, then add to the Text shelf of the Text marks card. Remove the original Run Rate field. You should still only have 1 run rate value displayed per row.

Now we can tidy up the display of this text. Ensure the Run Rate < Plan (red) and Run Rate > Plan (black) fields are on the same line of text with no spaces between, then colour the fonts to match the requirements

Finally, remove axis/row headers, tidy up gridlines etc, and adjust the width of the bars to suit.

Title Sheet

As the title needs to include the date, and to ensure it would be dynamic, I created a simple text sheet to display the title, and set the worksheet background to a light grey.

I then added both sheets to a dashboard, with both set to ‘Fit Entire View’, and titles hidden.

To get the Phone Layout display, I then selected the Phone option, clicked the padlock to Edit layout, and set to Fit all, and made adjustments to suit. The issue you might have though is that while things all look a bit squashed on your laptop display, it actually will render ok when published. This can unfortunately be a bit of trial & error.

My published viz is here.

Happy vizzin’ and stay safe!

Donna