Dynamic date parameter in tableau

Dynamic date parameter in tableau DEFAULT

For one of the exercises yesterday, we had to create a view that dynamically changed the date level depending on the user’s choice (year, quarter, month). Today, I’m going to show you how to do it.


As you can see, depending on the choice in ‘Select Date Level’ section, the view adjusts accordingly and shows the sum(Sales) at the year, quarter or month level of Order Date.

How To

1. Parameter

I’m going to use Sample – EU Superstore data set from Tableau for the purpose of this tutorial. To get the result we want, we need a parameter. This is exactly what the user will change. Let’s create the ‘Select Date Level’ parameter, set the data type to string and allowable values to List. It’s important what we put in the list: start with value field and type in ‘year’, ‘quarter’, ‘month’ all in separate rows, make sure they are all lowercase. Thankfully, we can change what the user sees, let’s change Display As to title case. This is what your settings should look like:

It’s important that you type in lowercase in Value field because anytime you change the Value, Display As will also update. However, if you change Display As, the value stays unchanged.

2. Calculated Field

If you ever worked with parameters, you know you need a calculated field to actually use the parameter functionality. If you’re new to parameters, now you know 😉

Usually, you create a field that checks the value of the parameter and then performs a certain function. Due to characteristics of dates and associated functions we can do it slightly differently, in a shorter way. Let’s create a calculated field called ‘Date Level Aggregation’:

What’s happening here: I used DateName function because it returns a string for the date part of the given date. For year and quarter, it will return the number as a string, for month, it will return the name of the month (eg. January). [Select Date Level] should be purple on your screen, as it is a parameter; it is also what we use as the date part. Because we’re using the parameter in the function, the values need to be lowercase so that Tableau recognise them as valid arguments.

Let’s create the view. Drag Date Level Aggregation on Columns shelf and Sales on Rows shelf. Right-click on the Select Date Level parameter and check ‘Show Parameter Control’. We want to allow only one option to choose. To get that, click on the caret in the parameter control (should be on your right-hand side) and select ‘Single Value List’. I also change the fit to Entire View. This is what your view should look like:

Hold on, we want the quarters to show up as ‘Q1’ not just ‘1’. Alright, calm down, we’ll fix it. Originally, I created the following If-Statement:

But with Jon‘s help we managed to make it even neater:

With the updated calculated field, you should get what was advertised. To improve the view, let’s hide the field labels for columns. There you go:


Sours: https://www.thedataschool.co.uk/hanna-nykowska/tableau-tip-dynamic-date-level-aggregation

Dynamic Date Calculations Using Sets in Tableau

Dynamic Date Calculations Using Sets in Tableau

The most common requirement in my experience when building out a business dashboard, is to provide the ability to be able to slice and dice the KPIs over multiple periods of time to get a sense if the metric is heading in the right direction or not. To do this, you would generally compute your metric for the current date period and then compare it against the prior date period value to showcase the increase or decrease. Adding another layer of complexity to this requirement would be to allow the end user to select the metric computation date period via a dropdown menu, and as a dashboard designer, the onus is on you to make this work in the most efficient way possible. Some of the examples of date periods could be year to date, rolling 12 months, same month prior year, prior month etc. For example, if the user selects year to date and then selects the month of April 2021, the KPIs should be computed for Jan 2021 to April 2021 as the current period, to be compared to Jan 2020 to April 2020 as the prior period.

Before I dive deeper into the technical aspect of how to perform date calculations like YTD, Rolling 12 Months, Prior Month (PM) and Same Month Prior Year (SMPY), I would like to stress the importance of two things on a Tableau dashboard:

1. Actionable Insights

2. Dashboard Performance

The actionable insights make it clear what actions need to be taken and how organizations should make data informed decisions to course correct if need be, whereas the dashboard performance determines how smoothly can the former be done. Coincidentally, date comparisons using sets can aid both to a significant extent even with a complex and heavy dataset.

The primary objective of this blog is to provide a detailed walkthrough on how to effectively incorporate date metric calculations (YTD, PM, SMPY etc.) using Sets to provide actionable insight for the respective metric and at the same time build on an important Tableau optimization principle of “Strings and dates are slow, numbers and Booleans are fast.

Sets are user created fields based on dimensions which at the highest level creates subsets of your data based on some conditional logic. If you are not familiar with Tableau Sets, I would recommend going through the Sets resource page on Tableau’s website before proceeding further.

I would be using the Tableau Sample – Superstore dataset to demo this technique. Please feel free to download the workbook to follow along. This blog will help guide you on how to build out sets and calculations to create dynamic date calculations in order to optimize your workbook performance and enable you to create actionable insights. Without further ado, let us jump right in.

Step 1: Create parameter “Comparison Period

The name could be anything, but the configuration should look something like below. This is the place where you would list all the comparison options that you would like the end user to have. For this writing, I will be dealing with the below four, however, this is not an exhaustive list but should be good enough to drive the point.

Step 2: Create parameter “Select End Month

This parameter will act as the reference month giving the user the freedom to compute metric for the month of their choice. For e.g. selecting April 2021 here would compute all comparison with reference to this month. YTD will sum sales value for the months from Jan 2021 to April 2021, Rolling 12-Months will sum sales from May 2020 to April 2021, so on and so forth.

You can make this a dynamic parameter by pointing it to the date field in your data source. In this case, I will be using the field Order Date. I then change the Display format to Month Year. You may see a red warning alert due to the repetition of values, but you can click ok and close it.

Select End Month

If in your design, you would like the current month to be the reference point always, then you can skip this step and replace this parameter with a calculated field that gets you the current month. DATETRUNC(‘month’,TODAY()) is one way to do it.

Step 3: Create calculated field to compute the last day of “Select End Month” parameter

This will be required for the calculations in following step, and it establishes the upper end date limit that would need to be considered for calculating YTD, SMPY, PM etc.

Step 4: Create a calculated field “Comparison Period Determinant”

This is the place where the date logic for each of the period gets computed. At a very high level, this logic will segregate the underlying dataset into 3 categories based on the date values present in the [Order Date] field:

Category 1: All rows of data having 0 assigned against them for this field represent the “Current” period. These are the dates that satisfied the “If” part of the below statement. For e.g. if the [Select End Month] parameter has April 2021 selected and [Comparison Period] parameter has Previous Month option selected, then within the “If” statement, the Case statement will bring the control to “When 3” and based on the logic all rows with [Order Date] of April 2021 will return true and hence would be assigned a value of 0 against them per the “then” statement. On the same lines, all rows with [Order Date] of Jan 2021 to April 2021 will be assigned value of 0 if you have YTD selected for [Comparison Period] parameter and so on and so forth for the other parameter options. 

Category 2: All rows of data having -1 assigned against them for this field represent the “Prior” period. These are the dates that satisfied the “ElseIf” part of the conditional statement. For e.g. if the [Select End Month] parameter has April 2021 selected and [Comparison Period] parameter has Previous Month option selected, then within the “ElseIf” statement, the Case statement will bring the control to “When 3” and based on the logic all rows with [Order Date] of March 2021 will return true and hence would be assigned a value of -1 against them per the “then” statement. On the same lines, all rows with [Order Date] of Jan 2020 to April 2020 will be assigned value of -1 if you have YTD selected for [Comparison Period] parameter and so on and so forth for the other parameter options.

Category 3: All other rows of data would have NULL value assigned to them as the logic in the calculated field does not handle them. This is perfectly alright and very much what we want.

Tip: The best way to check if your logic is working is to build a simple sheet like below and see what values are being assigned against each of the [Order Date] values by changing the two parameter values.

Step 5: Create Current and Prior Sets

To create sets of the calculated field we created in the above step, convert the “Comparison Period Determinant” field to dimension. Now create set “Current” of this field and select only 0 from the list. Then create set “Prior” by selecting just -1 from the list.

Create Set

The beauty of these sets is that they are highly optimized and dynamically segregate the data for current and prior period based on the selections in the two parameters even without being dragged on to the sheet’s filter pane. This is a very critical point to note, since it does not filter the data, you have both Current and Prior period data now available in the same sheet to be compared against each other, exactly what we want to do. For e.g., we can now compare sales of April 2021 with March 2021 and know if there was an increase or decrease.

Step 6: Build respective KPI calculated fields using the above sets

For every KPI, we build Current Period, Prior Period and % Change calculated fields. The screenshots below will be for one of the metric Sales, however, please feel free to download the workbook for checking out the others.

After building out each percent change calculations for each of our KPIs, we can now easily see which metrics increased/decreased in value over our chosen comparison period by using some custom formatting with up/down arrow indicators and coloring for negative/positive values. Now, our parameters will seamlessly change the percent change calculations allowing for quicker actionable insights and because of the way we leverage sets in our calculations, our dashboard will be performant as well.

Below is a screenshot of the redesigned dashboard.

Please note, that like all other things in Tableau, you can achieve the same result in other ways by not using sets and by just the calculated fields route. However, it may or may not be as efficient as sets depending on how you set it up and your underlying dataset. But this would be an interesting topic to be explored as a subject for another blog.

In my experience, sets technique is super performant even with a very dense dataset but, I will leave it for your exploration. Hope you were able to get some value out of this technique.

author image

About the Author

Over the past 11 years, I have been deeply involved professionally in the field of data management and business analytics as a business enabler for varied multinational corporations with disparate domains.

Rahul Prasad | Senior Business Intelligence Associate | USEReady

Sours: https://resources.useready.com/blog/dynamic-date-calculations-using-sets-in-tableau/
  1. Free ultimate survival system book
  2. Curved air air cut
  3. Jpc long side armor pouch
  4. Stanford health care jobs

Keep up with dynamic data changes using dynamic parameters

Dynamic parameters can be strings, measure values, or dates, offering flexibility and interactivity when building a dashboard for your audience. Because they can be easily used in most analytical entities, dynamic parameters give you programmatic control to customize your analysis. Settings are configured upon publishing so that interacting with workbooks is a fast, curated, and relevant experience to the person reviewing or consuming it. Let’s look at two examples using date and string parameters.

Dynamic date reference

This dashboard shows housing data for the city of Seattle. When I last published the workbook, I had access to data through August 2019.

Every time I get new data, I have to update the Reference Date parameter and republish my workbook so that everyone can explore the market using only the most-relevant, accurate content. With dynamic parameters, I only have to set it up once and Tableau will automatically update the list of values and the parameter value every time someone opens the workbook. Now, as I receive new housing data in October, this dashboard will update automatically to reflect the latest values. In just two clicks, everyone is now able to see the right data at the right time.

User-based server settings

In this next example, I have access to a table that maps Tableau Server user IDs to their companies. When I view the workbook logged in as “Filippos,” I want my company, “Blammo,” to be highlighted and to see my company’s percent change in stock growth labeled.

To set this up, I have created a single-value, string calculation expression that is assigned to the Logged-in user’s Company parameter. The expression appears as follows:

With default parameter values and without having to join the two tables, I can assign the result of the above calculation to my parameter and apply that anywhere I want. Now, if I view the workbook while logged-in as a different Server user (userid: blyons is mapped to “Company X”), the highlighted company changes automatically to “Company X” and is appropriately labeled when the workbook opens.

I could have configured the Date Reference parameter to update to the latest month or some other calendar value—whatever provides the most impact to my consumers. What’s more, anyone can go ahead and change the parameter to a different company and see the percent change in stock growth for that company. You can now deploy and easily support personalized and fully interactive, end-to-end analytic applications.

Sours: https://www.tableau.com/about/blog/2020/2/say-hello-dynamic-parameters
How and Why to use Dynamic Parameters - Tableau in Two Minutes

In the history of the Tableau ideas forum, dynamic parameters (often referred to as Hooperized Parameters, named after original Zen Chuck Hooper) is the most upvoted feature request.  Lucky for all of us, the Tableau dev team has delivered this feature to us in Desktop version 2020.1, which was released late last night!!!!  I recently spent some time testing them out.  I personally got tripped up in one area, so let me take a few minutes to tell you about their purpose, how they work, the key to making them work, then provide you with a few good use cases.

Dynamic parameters are the same as standard parameters, but with two important added advantages.  First, you can allow your parameters to be updated with values from a field from your data source when the workbook opens.  For example, let’s assume you refresh your extract monthly to include the previous month.  If you are using a date parameter, you can allow that list to be updated with all values from your date field, including the new values (i.e. the newest month).  The values are no longer static and no longer need to be updated manually.  The second advantage is that you can set your parameter to be automatically populated with a value of your choosing.  Using the date example from before, when updating your date parameter list with all values, you can set your date parameter to automatically choose the most recent date.  (Note that my examples focus mainly on dates, but dynamic parameters can be utilized with any data type, not just dates). 

Automatic Population

Let’s first talk about automatically populating your parameter using a specific field.  For this example, I created my own data set that shows a value for each month of 2019 – I encourage you to download this simple data source and follow along with this blog post.  If you are unable to access this via Google Drive, please email me at [email protected] and I will send it to you (or simply recreate it, as it is quite simple). 

In this example, we are going to assume that we are using multiple data sources and plan to use a date parameter to easily filter across those data sources. 

To start out, let’s look at what this parameter would look like in previous versions of Tableau.  Go ahead and connect to this data set in Tableau 2019.4 or earlier.  Create a parameter and call it Date Parameter.  Set it to a data type of Date and make it a List parameter.  Click Add from Field and click on Date (from the data source) as shown below.  When you do so, it will populate the parameter with all the dates in the data source, each month for 2019. 

The problem is…that’s it.  The values are hardcoded into the parameter and will not change unless you manually update them (something I do regularly with my work dashboards).  So how do we fix this?  You already know the answer to that question – dynamic parameters. 

Now connect to the data source using Tableau 2020.1 and create a parameter.  Like before, set the data type to Date and Allow Values to list.  Below this option, you will notice some differences from what you are used to seeing.  The first option is “Fixed”.  Here you can feed in values from your data set and it will remain fixed, just like in previous versions.  If you choose Fixed, the parameter will act exactly like they did in the past. 

However, we can change this to be a dynamic parameter by clicking on “When workbook opens”.  When you do so, you are given the same exact options as before, meaning you can feed in values from a field in your data source and it will populate the parameter list.  However, when using this option, your parameter will be automatically updated when your data from that field is updated.  For example, the data source shows each month from 2019; let’s assume I added Jan 2020 to the data set.  If I utilized this option, the Jan 2020 data would be automatically added to my parameter list the next time the workbook is opened.  This is important to note, the parameter update only happens when the workbook is opened in either desktop or where it is published.  Parameter list values do not update with a refresh.  (Screenshots are from the 2020.1 Beta so may differ slightly from the actual version).  

So let’s go ahead and try it.  Add 1/1/2020 to the data source spreadsheet then save it.  Save your Tableau workbook and close it.   Now reopen the workbook.  Look at the parameter and you will see the Jan 2020 value added to the bottom of the list.  (If you do not, make sure you are connected to the correct data source and not some temp version of the data).

Default Value

When you viewed the above screenshot, you may have noticed another option that was not previously available.  That option is “Value when workbook opens”.  You can set your dynamic parameter to default to a specific value when the workbook is opened. Click on this drop-down menu.  The only option you should see is “Current Value”:

Okay, we can set it to the current value, but that doesn’t really do us much good.  In this case, we need to create a calculated field to feed into this and use that calculated field to set the default value of the parameter.  So let’s stick with our example from above.  We have data for each month from 2019 and have added Jan 2020.  At any point in time, we want our parameter to default to the most recent date – in this case, Jan 2020.  If we want to get the latest date, we would want to grab the MAX date from the list of dates, correct?   So let’s create a calculation called “Most Recent Date” with a formula of MAX(Date). 

Now click to edit the parameter.  In the “Value when workbook opens” field, choose “Most Recent Date”.  Wait...only “Current Value” shows. What happened?  Where is “Most Recent Date” field?  Well I did the same exact thing in my first attempt.  Thankfully, I learned from Filippos Lymperopoulos (a Product Manager at Tableau) that this field must be “viz-independent” (this is the "key" that I referenced in the title of this blog post).  MAX(Date) would be dependent on the viz, so we can use a fixed LOD calculation to address this.  So let’s change our Most Recent Date calculation to be a fixed LOD: {FIXED : MAX(Date)}.  As a side note, this can also be expressed as {MAX(Date)}.  

I'd like to add a few notes here.  First, although the field used for the "Value when workbook opens" has to be viz-independent, it does NOT necessarily have to be a fixed LOD.  You could create a calculation of TODAY() and since it is viz-independent, it will work as well.  Second, if using a Fixed LOD, it cannot be a nested LOD.  For example, {FIXED : MAX([Order Date]-52*7)} works where  DATE(DATEADD('week', -52 {FIXED: MAX([Order Date])})) does not work.  Basically, you must start your calculation with the fixed LOD, it cannot appear inside of another calculation.  

Okay, with the sidebars out of the way, go ahead and edit the parameter again and choose the drop-down next to “Value when workbook opens”.  You’re Most Recent Date calculation should show.  Go ahead and choose that value.  This sets your parameter to use this calculation each time the workbook opens, which means it will choose the latest date from your Date field automatically upon opening.  How freaking awesome is this????!!!!  Go ahead and try it; set your parameter to a past value, save the workbook, close and reopen.  It will automatically set that parameter to the latest date.  You can then use that parameter with a calculation to filter your view. 

As I mentioned before, this is not applicable to just dates.  You can do it with any parameter data type. 

A Couple of Good Use Cases

Date Range:  I believe that dates will be the most common way in which dynamic parameters are utilized.  The information above showed how to do it with just a single date, but what if you want to do this with a date range?  Well, we can utilize the same methodology above with a few extra parameters and calculations to do exactly that. 

In order to do this, we are going to use the same sample data set and Date Parameter we previously set up (we are basically going to piggyback off the work we already did).  Let’s set up two more parameters: Time Frame and Time Frame Value.  The Time Frame parameter will be a string containing a list of values: day, month, year (lower case).  These can be fixed as we do not need them to be updated based on our data – they can remain static.  The Time Frame Value will be a list of integers from 1 to 31 and can also be a static parameter.  Show those parameters on the screen. 

The plan is to create a date difference calculation based on these three parameters then create a date filter to only show the corresponding time frames.  For example, our data is showing months, so let’s set our Time Frame Value to 6 and Time Frame to month.  I set the Date Parameter to any value for now (12/1/19).  The idea is that when we open the workbook, it will set the Date Parameter to the most recent date and show a total of 6 months (the most recent month and the 5 months prior). 

Create a calculation called “Date Filter Time Frame”:


IF DATEDIFF([Time Frame], [Date], [Date Parameter]) >= 0

   AND DATEDIFF([Time Frame], [Date], [Date Parameter]) < [Time Frame Value]




This calculation uses the time frame to calculate the difference between the date in our data and the date parameter.  It will ensure that the date difference is at least 0 and that it is less than the selected Time Frame Value. 

Add “Date Filter Time Frame” to the filters shelf and check only “SHOW”; do not check “HIDE”.  Add Date to the rows and set it to Exact Date and discrete.  You should only see a subset of your values.  If you used the same values as I showed above, you should see 7/1/2019 – 12/1/2019. 

Save the workbook and reopen it.  The Date Parameter should update to the most recent Jan 2020 date (that we added above) and your view should filter to the previous 5 months: 8/1/2019 – 1/1/2020.  Since you provided the Time Frame and Time Frame Value parameters, you can then allow your user to adjust the date to anything they like (and you can easily change the default settings). 

Dynamically Pull Different Tables from Your Database:  This comes from an actual use case at work.  Each day, we create a snapshot table of our current inventory.  We retain snapshot tables every day for 90 days and prior to that is stored as a monthly snapshot table only.  So on February 16, we would have tables that would look like:












One of our most widely utilized dashboards looks at the information supplied in these tables.  We typically review the most current data, but often have a need to look back at previous dates in time.  In this case, we couldn’t just filter the data; we needed to pull data from a completely different table.  We have hundreds of tables, so combining the tables simply wasn’t reasonable.

The goal with this dashboard was to automatically connect to the most recent table while allowing users to pull data from any date (any table).  We were able to implement a combination of custom SQL and several extensions to make this dashboard work…but it’s not optimal in any way. Dynamic parameters will make this process very simple and in fact, I’ve already mocked it up in the 2020.1 pre-release.

I have a table in my database that lists all of the snapshot table names (like the examples above).  So the first step was easy; I connected to this table via Custom SQL to pull just this Table Name field into a Tableau workbook.  Next, I created a Table Name Parameter.  It was set with an integer data type and list, which was populated with the Table Name field (using “When workbook opens”).  I then created a Fixed LOD to pull the most recent table name, { FIXED : MAX([Table Name])}, and set that as my “Value when workbook opens”. 

Okay, now remember that the above are just values from a field in a table, they don’t contain any actual data.  The ultimate goal is to pull actual data from the actual tables with those names, have it default to the most recent table, but allow users to choose any table.  As an example, let’s saying I’m pulling from the database called Inventory.  I want the dashboard to initially pull all data from inventory.dbo.20200215.

To do this, I created a new data source that utilized another Custom SQL statement.  To the Custom SQL window, I added “SELECT * FROM inventory.dbo.” then inserted the Table Name Parameter as shown below:

When this custom SQL loads, it pulls the table corresponding to the parameter.  And since we set up the parameter as a dynamic parameter with a default value of MAX(Table Name), that means when the workbook is opened, it pulls data from the most recent table AUTOMATICALLY!!!  In addition, the user can use the parameter to select any table in history.  Most importantly, any time a user opens the dashboard, it will check to see if there are different values and update the parameter with those new values.  Incredible!

Wrap Up

Dynamic Parameters are the most requested feature in the history of the ideas forums…and there is a reason for that.  They are incredibly powerful and will automate so much of the manual work that analysts have to do on a recurring basis.  And the use cases described above only scratch the surface of what is possible.  I must say thank you to Tableau and the Tableau dev team for continually providing us with these powerful features!!!!!!!

One last note, I mentioned Filippos earlier in this post.  He wrote a very good overview of dynamic parameters on Tableau’s website and provided a nice use case related to user-based settings.  I recommend you check it out. Thanks for reading and as always, feel free to contact me at any time if you ever have comments or questions.  Enjoy 2020.1!

Kevin Flerlage, February 25, 2020

Twitter | LinkedIn | Tableau Public

Sours: https://www.flerlagetwins.com/2020/02/the-key-to-dynamic-parameters-some-good.html

In dynamic tableau parameter date

Take off everything except golf. I moved my legs, the panties fell and I just stepped over them, then pulled off my T-shirt. Click. Mom took a picture of my breasts. - Lie on the bed, bend your knees, spread your legs to the sides.

How To Create a Filter for Start and End Dates Using Parameters in Tableau

The swords blaze as Tari fights off all six at once, standing in the center of a circle of crystal turrets. Finally, a signal sounds that they managed to get it over a hundred times. The Elans go to the columns. The time has not yet run out, but the fight is over.

You will also like:

She's also teasing. But Olga is unusually sweet, she drags me under the arm to have breakfast, cheerfully chirping something on the go and from time to time snuggling. Up to me with her whole body. In the dining room, she puts her palm on my fly a couple of times, as if by accident.

Here, women, you will not understand them.

970 971 972 973 974