
1. Re: Bins for dates  30, 60, 90 days
Neal Smoller Jul 8, 2013 8:19 PM (in response to Neal Smoller) 
2. Re: Bins for dates  30, 60, 90 days
Jim Wahl Jul 9, 2013 12:03 AM (in response to Neal Smoller)1 of 1 people found this helpfulHi Neal,
It sounds like you have a discrete bin dimension, which makes it easy to right click on the measure pill and select quick table calculation > percent difference. Then you can add the measure again to the view so that you have both the measure value and the % diff version of it.
How are you calculating the bins? I think that if you're using Tableau's bin feature on "days since" calculation, the 0, 30 and 60 bins refer to dates between 0 and 30 days, 30 and 60 days, and 60 and 90 days, respectively. This is a little confusing to me. You could also calculate the bins manually with something like
IF DATEDIFF('day',[created], today()) < 30
AND DATEDIFF('day',[created], today()) >= 0 THEN "30D" // not necessary, but helpful if you use a param date
ELSEIF DATEDIFF('day',[created], today()) < 60
AND DATEDIFF('day',[created], today()) >= 30 THEN "60D"
...
END
One other tip is that a great way to understand table calcs better is to study the Tableaugenerated formulas. Rightclick on the % difference pill and select edit table calculation > customize.
Jim

3. Re: Bins for dates  30, 60, 90 days
Neal Smoller Jul 16, 2013 12:55 PM (in response to Jim Wahl)ok it's looking good!
is there anyway to now do the "arrows thing" that the example i attached has... i figure i can create 6 images, then sub the image for the numbers. I just don't know how
secondly, is there a way to have the number data side by side with the % data, as in the example...
i.e.
30 60 90 30 60 90
Product A 1500 1300 200 + +++

4. Re: Bins for dates  30, 60, 90 days
Jim Wahl Jul 16, 2013 1:44 PM (in response to Neal Smoller)is there anyway to now do the "arrows thing" that the example i attached has... i figure i can create 6 images, then sub the image for the numbers.
Yes, using a calculated field and custom shapes. The custom field will define the bins; for example:
IF [%diff] > 0.5 THEN "High improvement"
ELSEIF [%diff] > 0.2 THEN "Mod. improvement"
ELSEIF [%diff] > 0 THEN "Small improvement"
...
END
You'll need to create a %diff calculated field to do this. You can just click on the quick table calc, select edit table calc > customize and Tableau will open a create calculated field dialog box with the quick table calc formula.
Then you can drop this field on the shape shelf. Tableau will assign default shapes, but you can easily add custom shapes. See Custom Shapes.
secondly, is there a way to have the number data side by side with the % data, as in the example.
You can put the numbers sidebyside by moving the measure names to the col shelf.
BUT, combining the shapes with the multiple measures, where some of these measures are table calcs may will be tricky. Or it could be very easy if you just use multiple worksheets in a dashboard layout container. But if you want everything on one worksheet, a good place to start would be Jonathan Drummey's conditional formatting examples. conditional formatting  Drawing with Numbers
If you get stuck, it's usually easier to attach a sample packaged workbook (twbx) with some nonconfidential / sample data, and people can more easily provide recommendations.
Jim

5. Re: Bins for dates  30, 60, 90 days
Neal Smoller Jul 18, 2013 12:27 PM (in response to Jim Wahl)Last problem, probably not a tableau thing:
my bins are now creating a "NULL" for 30, 60, 90 days. I looked at underlying data and it says "3/22/2013" for all the entries. Thoughts on why?
And, if i'm correct, the %diff is basically checking the column to the right (the greater days or farther date out) and comparing it to see what that % diff is? So, theoretically, i could exclude >120 days and only HIDE 90120?
Thanks again!

6. Re: Bins for dates  30, 60, 90 days
Jim Wahl Jul 19, 2013 1:41 AM (in response to Neal Smoller)The answers to your questions depend somewhat on how you've got your formulas setup and your underling data. It's sometimes a lot easier to mock this up with some dummy data or the superstore data and provide a sample workbook in the forums.
I don't know why your underlying data is 3/22/2013unless this is a calculated fieldTableau is not modifying the underlying data.
And, if i'm correct, the %diff is basically checking the column to the right (the greater days or farther date out) and comparing it to see what that % diff is? So, theoretically, i could exclude >120 days and only HIDE 90120?
This depends on how you've setup %diff. By default it's looking one value to the left by using LOOKUP([measure], 1). But you could have it go one to the right. Since the leftmost (or rightmost) column will be null for %diff, you're right that you can hide this to remove it visually, but keep the data and formulas in tact.
Again, I'm guessing a little bit with these answers and it would be easier with a sample (packaged) workbook to work with. Often people will mock something up with the Tableauprovided superstore (subset) data.
Jim

7. Re: Bins for dates  30, 60, 90 days
Suhrid Ghosh Jan 26, 2014 9:15 PM (in response to Jim Wahl)Hello Jim ,
How you dong ?? Long time ... Just needed some help on the same issue . I wanted to calculate the bins and the method where you mentioned something like this :
IF DATEDIFF('day',[created], today()) < 30
AND DATEDIFF('day',[created], today()) >= 0 THEN "30D" // not necessary, but helpful if you use a param date
ELSEIF DATEDIFF('day',[created], today()) < 60
AND DATEDIFF('day',[created], today()) >= 30 THEN "60D"
...
END
Here instead of today() i wanted to take max date of my [Sales Date] something like this DATEDIFF('day',[Sales Date], max[Sales Date]) . Facing some issues on this . How can i achieve this ?
Thanks & Regards,
Suhrid Ghosh

8. Re: Re: Bins for dates  30, 60, 90 days
Jim Wahl Jan 28, 2014 2:19 AM (in response to Suhrid Ghosh)Hi Suhrid,
Given the ease of binning of dates by TODAY() or a parameter, it's surprisingly difficult to change TODAY() to MAX(Date). There are a few solutions to this. I've outlined two below.
But first, a little bit on why this is difficult.
 If you just drag Date to the view, click the pill > Measure > Maximum, you'll get the maximum data in the data.
 But as soon as you add additional dimensions to the view, the MAX(Date) is calculated for each member of the dimension.
 You can use a table calculation to aggregate (find the max) at a different level of detail. TOTAL(MAX([Order Date]))
But try substituting this into the binning calculation you have above, and you're in for a bit of frustration.
 Since TOTAL(MAX(Date)) is an aggregate, all dates in the calculated field must also be aggregates.
IF DATEDIFF('day',MAX([Order Date]), TOTAL(MAX([Order Date]))) < 30 AND DATEDIFF('day',MAX([Order Date]), TOTAL(MAX([Order Date]))) >= 0 THEN "30D" // not necessary, but helpful if you use a param date ELSEIF DATEDIFF('day',MAX([Order Date]), TOTAL(MAX([Order Date]))) < 60 AND DATEDIFF('day',MAX([Order Date]), TOTAL(MAX([Order Date]))) >= 30 THEN "60D" END
 Now, you can add this to the view. Order Date must also be in the view, since we want the DATEDIFF calculation for each day. This looks like it's getting close. Tableau calculates the sum of sales for each row (day) in the Order Date partition and bins it based on the Date Bin calculation above. The sum of the 30D column is correct.
 Now you might be thinking, we can just WINDOW_SUM(SUM(Sales)) all of the values in the 30D column. But you can't because you can't partition a table calc such as WINDOW_SUM() by another table calc, in this case Date Bins TC.
So, this approach unfortunately doesn't work.
Alternative approaches. There are two general approaches that come to mind.
1. Add the max data as a column in your data source. The advantage is now you can use your existing formula and just replace TODAY() with the new column name, in my example Order Date Max. With an Excel data, click on Edit Data Connection > Custom SQL and add the [Order Date Max] to the SELECT statement. The disadvantage of this approach is that Custom SQL is often slow with bigger data sets (unless you use an extract). More importantly, if you update the data source with new columns, you need to update the custom SQL.
SELECT [Orders$].[City] AS [City], [Orders$].[Customer ID] AS [Customer ID], ... [Orders$].[Unit Price] AS [Unit Price], [Order Date Max] FROM [Orders$], (SELECT MAX([Orders$].[Order Date]) AS [Order Date Max] FROM [Orders$])
2. Create calculated fields for each interval using table calcs. For example Sales 30D =
IF FIRST() == 0 THEN WINDOW_SUM(IIF(DATEDIFF('day', MAX([Order Date]), [TOTAL(MAX([Order Date]]))]) <30, SUM(Sales), 0)) END
Note that [TOTAL(MAX(Order Date))] is the calculated field from above, not the formula TOTAL(...). This is important, because to find the max date in the data, this table calc needs to be computed over all dimensions in the view, including Customer Segment. But the Sales 30D table calc should be calculated over Order Date only to compute the sum for each customer segment.
If you just use the formula in the TOTAL(MAX(Order Date)), you can only configure a single compute using. In this case Order Date, which means that the formula will find the max date for each Customer Segment, not the max date across all segments.
To setup this nested table calc, add it to the view. Click the pill > Edit Table Calculation > Advanced. From Calculated Field pulldown menu, select TOTAL(MAX(Order Date)) and move all dimensions to the right, Addressing/Compute Using side. Compute using for the Sales Last 30D should just be Order date.
Now you can use Measure Names / Measure Values to place these in the view.
There are probably other ways to accomplish this, particularly if you were using week or month for the date bins instead of 30 days and 60 days. Good luck.
Jim

Book4.twbx 1.5 MB

9. Re: Bins for dates  30, 60, 90 days
Suhrid Ghosh Jan 28, 2014 4:56 AM (in response to Jim Wahl)Hello Jim ,
Thanks for the your efforts and your effective solution .
Thanks & Regards,
Suhrid

10. Re: Re: Bins for dates  30, 60, 90 days
Jim Wahl Jan 28, 2014 5:24 AM (in response to Suhrid Ghosh)Here is a v8 version.

MaxDateExample_v8.twbx 180.2 KB


11. Re: Bins for dates  30, 60, 90 days
Jonathan Drummey Jan 28, 2014 6:18 AM (in response to Jim Wahl)Hi Jim & Suhrid,
There's a third option that I think might work in this case, and that's to use a selfblend to get the Max Date. Then the date calcs could be regular aggregates, and if you need to partition on them then you can turn off Ignore in Table Calculations.
Another advantage here is that by turning on and off the linking dimensions you can control the level of the blend.
I'm traveling today so I won't have a chance to put together an example, if you need one let me know!
Jonathan

12. Re: Re: Bins for dates  30, 60, 90 days
Jim Wahl Jan 28, 2014 7:32 AM (in response to Jonathan Drummey)Great idea. I always forget about the "Ignore in Table Calculations" option.
Here are the steps I used.
1. Duplicate the data source.
Click on the data source > Duplicate. This is what creates the "selfblend." You're not really duplicating the data, but creating a duplicate connection that can be controlled independently.
2. Create a calculated field for Order Date Max Blend
I did this in the primary data source. It's just: MAX([Sample  Superstore Sales (Excel) (copy)].[Order Date])
3. Edit the original Date Bins field. As with the table calc version above, Order Date Max Blend is an aggregate so all fields need to be aggregates.
IF DATEDIFF('day',MAX([Order Date]), [Order Date Max Blend]) < 30 AND DATEDIFF('day',MAX([Order Date]), [Order Date Max Blend]) >= 0 THEN "30D" // not necessary, but helpful if you use a param date ELSEIF DATEDIFF('day',MAX([Order Date]), [Order Date Max Blend]) < 60 AND DATEDIFF('day',MAX([Order Date]), [Order Date Max Blend]) >= 30 THEN "60D" END
4. Add this to the view along with Sales. Add Order Date to the Detail buttonclick the pill and change it to Exact Date.
You'll need to click on the secondary data source and unlink the Order Date field. (Click the orange link to break it and make it gray.)
If no fields are linked, we'll get the max date in the data source, regardless of what's partitioned or filtered in the primary data source.
You could also selectively link fields. For example, if you wanted the max data for each customer segment, you could link the customer segment fields. (I've also excluded the Null values below.)
5. This looks identical to the original table calc version that dead ended in my replay above. The difference here is that AGG(Date Bins Blend) is not a table calc and there's an option to use it to partition table calcs.
First create a new filed WINDOW_SUM(SUM(Sales)) to sum the values in each pane. Note you'll need to wrap this in an IF FIRST() ==0, because the value will be calculated for each row in the Order Date dimension.
6. Now the magic. Click the AGG(Date Bins Blend) and deselect Ignore in Table Calculations. Finally, from the top menu, click Analysis > Stack Marks > Off
Jim

MaxDateExample_3ways_v8.twbx 277.5 KB


13. Re: Bins for dates  30, 60, 90 days
Jonathan Drummey Jan 29, 2014 10:55 AM (in response to Jim Wahl)Hi Jim, thanks for taking my suggestion and running with it!
On Tue, Jan 28, 2014 at 10:32 AM, Jim Wahl <

14. Re: Bins for dates  30, 60, 90 days
MANOJ PRABHAKAR Sep 27, 2017 7:25 AM (in response to Neal Smoller)