4 Replies Latest reply on Jun 28, 2017 10:54 AM by Matt Lutton

# Breaking profit down based on days between to dates

Hey All,

Looking for best method to look at a KPI performance based on the number of days between two dates.   Would like to have the user to be able to pick the days between the two dates and then show KPI performance based on all cases under that number and all cases above that number.

I've included a workbook example to build off.   In this, lets use average profit and Profit Ratio as the KPIs.   We're trying to determine profit and profit ratio if the number of days between order date and ship date are equal to or less than the number in the filter and profit for those that have a number greater than the number input.  Also would like to see the number of records in each population.

• ###### 1. Re: Breaking profit down based on days between to dates

Hi Mike,

Glad to see you pushing Tableau to its full potential! I am a trainer with Tableau and believe I can help.  However, I'm a bit confused.

In the workbook, I don't see any option for the user to enter a number, or anything showing a "number in the filter" that you mention.  Can you provide a detailed mock-up of your expected view, using screengrabs/paint or similar?  If you're looking for the user to choose two dates, and calculate between them, then you're likely looking for a parameter-based solution, perhaps using two parameters.  But I could be totally misunderstanding what you're after.

The more you can add to your workbook the better -- if you're not familiar with parameters, take a look here:

If you can add a mockup and some more detail, I'll be happy to take another look.  Cheers!

• ###### 2. Re: Breaking profit down based on days between to dates

Here are some relevant Google search results that are likely to help as well, searching only our Tableau Community:

Google search results on "datediff user chooses start and end date site:community.tableau.com"

• ###### 3. Re: Breaking profit down based on days between to dates

Hi, your comment about parameters actually led to me finding the solution.

What I did was create a parameter using Float from 1 to 365.

Then :

• created a datediff calc to determine the day spread of each case
• created measure to that said if [KPI] => than [Parameter] then 1 else 0
• created calc to look at all '1's in the above calc to find the average of the [KPI] for all cases that were under the parameter customizable days
• Did the same calc, but looked at all that were greater

It involved quite a bit custom calculations to obtain this, still wonder if there is a less redundant way to approach this.

Either way, thanks for the idea that sparked the solution.

• ###### 4. Re: Breaking profit down based on days between to dates

No worries, my friend!  There may well be other solutions out there... that's part of the fun of learning Tableau!    Cheers!