I Love Parameters

I spent time with a couple of co-workers yesterday going through revisions on an existing dashboard and received valuable constructive feedback. One person said the visuals are great and you built exactly what I asked for but I find it hard to answer questions – I end up having to do the work in Excel.

The thing they struggled with the most was looking at before and after a specific date. They needed the ability to pick the date to pivot off of and the ability to select the number of days back and forward to look at.

This was a good challenge for me and after thinking it over for a while I thought that parameters were exactly what I needed.

My idea was to create a parameter for the X date (the date to look back and forward from) and then create a parameter for the days back and the days forward and use those to limit the dates in the chart and use them to calculate the before and after measures they were looking for.

My Parameters are:

  • Selected Date – date to pivot back and forward from (date)
  • Days Back – # of days to go back (integer)
  • Days Forward – # of days to go forward (integer)

My Calculated fields are:

  • Back Date – [Selected Date] – [Days Back]
  • Forward Date – [Selected Date] + [Days Forward]
  • Custom Date Range – IF [Order Date] >= [Back Date]
    AND [Order Date] <= [Forward Date]
    THEN [Order Date]
    END

I then added the Custom Date range to the filters to exclude the nulls. To exclude the nulls I added the Custom Date dimension and selected Range of Dates > Special > Non Null

I used these fields to create a line chart to show the trend during the custom date range and added a reference line for the selected date. I also create a metric tile to show the before and after counts and changes in those counts. I added a few other charts to help them see what changed by different dimensions.

I love the flexibility with parameters!

I wanted to see if I could figure this out without Googling how to do this. I’d love to know if there is an easier way to do this or if I over engineered the solution.

 

4 thoughts on “I Love Parameters

  1. Hi Kate! I think your solution isn’t ‘over engineered’. It is quite elegant!

    It is interesting that you decided to let the user pick the number of days to go back and forward. I haven’t seen this logic before. I guess it useful sometimes to try to come up with a solution on your own 🙂

    In case you are curious, here is an approach that I’ve taken to solve a similar problem: http://www.olgatsubiks.com/single-post/2016/11/07/Current-Period-Vs-Previous-Period-Comparison-in-Tableau

    Also, thank you for joining the Data for a Cause! I’ve visited your blog through the link you’ve provided and enjoyed your articles! I’m looking forward to seeing articles about your Data for a Cause visualizations!

    Like

    1. Thanks Olga – the user selected days was a request from the end user – they wanted the ability to select what they wanted for a pre/post comparison. Your post on the time period comparisons is great – easy to follow! I’m looking forward to being a part of Data for a Cause as well!

      Have a great day
      Kate

      Like

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