1 2 Previous Next 15 Replies Latest reply on Sep 27, 2017 10:20 AM by Shinichiro Murakami

    Bins for dates - 30, 60, 90 days

    Neal Smoller

      I need to create something like the attached screenshot...

       

      Basically, I need percent change of products or sales or whatever over the last 30, 60, and 90 days.

       

      I'm sure it's calculations, but I'm horrible at that.

       

      I can do the graphical stuff myself

       

      The sales data would be presented as follows:

       

      Invoice Number, Date, Product, Extended Price

        • 1. Re: Bins for dates - 30, 60, 90 days
          Neal Smoller

          update, i've got bins setup for 30,60,90 etc by creating a "days since" calculated field

           

          datediff('day',[created],today())

           

          i just dont know how to now get the % diff between the columns...bins.tiff

          • 2. Re: Bins for dates - 30, 60, 90 days
            Jim Wahl

            Hi 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 Tableau-generated formulas. Right-click on the % difference pill  and select edit table calculation > customize.

             

            Jim

            1 of 1 people found this helpful
            • 3. Re: Bins for dates - 30, 60, 90 days
              Neal Smoller

              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      +    +++  

              diff.JPG.jpg

              • 4. Re: Bins for dates - 30, 60, 90 days
                Jim Wahl
                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 side-by-side 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 non-confidential / sample data, and people can more easily provide recommendations.

                 

                Jim

                • 5. Re: Bins for dates - 30, 60, 90 days
                  Neal Smoller

                  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 90-120?

                   

                  Thanks again!

                  • 6. Re: Bins for dates - 30, 60, 90 days
                    Jim Wahl

                    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/2013---unless this is a calculated field---Tableau 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 90-120?

                    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 left-most (or right-most) 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 Tableau-provided superstore (subset) data.

                     

                    Jim

                    • 7. Re: Bins for dates - 30, 60, 90 days
                      Suhrid Ghosh

                      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

                        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.

                        2014-01-28_10-07-46.png

                        • But as soon as you add additional dimensions to the view, the MAX(Date) is calculated for each member of the dimension.2014-01-28_10-09-22.png
                        • You can use a table calculation to aggregate (find the max) at a different level of detail. TOTAL(MAX([Order Date]))

                        2014-01-28_10-12-01.png

                         

                        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.

                        2014-01-28_10-37-07.png

                        • 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.

                        2014-01-28_10-41-36.png

                         

                        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$])
                        

                         

                        2014-01-28_11-22-49.png

                         

                        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 pull-down 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.

                        2014-01-28_12-04-04.png

                         

                         

                        Now you can use Measure Names / Measure Values to place these in the view.

                        2014-01-28_12-10-19.png

                         

                        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

                        • 9. Re: Bins for dates - 30, 60, 90 days
                          Suhrid Ghosh

                          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

                            Here is a v8 version.

                            • 11. Re: Bins for dates - 30, 60, 90 days
                              Jonathan Drummey

                              Hi Jim & Suhrid,

                               

                              There's a third option that I think might work in this case, and that's to use a self-blend 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

                                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 "self-blend." 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 button---click 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.)

                                2014-01-28_17-10-47.png

                                 

                                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.

                                2014-01-28_17-24-08.png

                                 

                                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

                                2014-01-28_17-26-40.png

                                 

                                Jim

                                • 13. Re: Bins for dates - 30, 60, 90 days
                                  Jonathan Drummey

                                  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

                                    Tanq ..that was very useful..how to edit bin size with a 15 second interval for time

                                    I have used this DATEDIFF('second',[Start Time],[End Time])/86400

                                    But my bin size goes crazy ..Please find attached image

                                    1 2 Previous Next