10 Replies Latest reply on Sep 24, 2017 1:56 PM by Jim Dehner

    In/Out Inventory Activity

    Dana Chaffin

      Searched the forum and web with little success - it's not a new question but I haven't found anything that works with my situation

       

      Below is an Excel chart on what I would like to accomplish

       

      How the data is captured and structured:

      • Activity Start Date and Activity End Date are in different columns (see attached Excel sample). If an activity isn't completed, the End Date will be NULL (and filled in when completed)
      • It's in an Access Database with over 1 million rows (so I can't pivot the data or export to Excel to pivot)
      • Notifications are sent daily for outstanding activity or any new activity. This creates a new row in the database for each notification. That means that I have duplicate control number/start date/end date rows (I know, count distinct, it's just something to be aware of)

       

      Things I've reviewed/tried:

      • Data blending (limited success)
      • Scaffolding (didn't fully understand the technique and wasn't successful)
      • Searched for Inventory, Burndown, and similar threads and tried those solutions (most of them recommended pivoting the data, which I'm not able to do)

       

      I built the logic and calculations in Excel to get the concept (see attached Excel sample) with a sample chart (below) to demonstrate my need

       

      7-27-2017 8-13-51 AM.png

      Any help would be greatly appreciated

       

      Thanks

       

      Dana

        • 1. Re: In/Out Inventory Activity
          Galen Busch

          Hi Dana,

           

          I'd love to help out, but I'm having a difficult time understanding your desired state.

           

          Can you lay out your business case, how nulls should be handled, value from this chart, etc?

          • 2. Re: In/Out Inventory Activity
            Dana Chaffin

            Thanks for the quick reply.

             

            The data from the access database reflects when an issue/action is identified and when the action is completed

            NULL in the End Date reflect that it's still an open issue/action

             

            The chart, when broken out by category for example (not in the data), would help determine where additional training or communication is needed

             

            Also, it will also help see if the training and communication was effective by seeing a drop in issues/actions

             

            Does that make sense?

            • 3. Re: In/Out Inventory Activity
              Jim Dehner

              Hi Dana

               

              This is a real interesting problem for a couple of reasons - first you can't pivot the data which would be the first approach - second your calculation is not a typical running total

               

              The first part - you need to have a framework to serve as the basis for your date calculations - a continuous m/d/y field that you then use a cross data base join to tie back to your In/Out data

              Ok that sounds nasty **** it really is not all that difficult

              - I added a sheet your excel that is nothing more than a list of dates from you earliest to the last date in your data set - I used an excel sheet you could probably create it using sql or something in your source data

              Then you bring both sheets into Tableau and join them as shown below -

               

               

              The join is created by opening the drop down on the 2 data sources and selection Edit Calculation - a box will open and input      =1  then OK

               

              Then go to the sheet 1 tab

              Now create a series of calculations to count the pluses and minuses and then to do the running sums

               

              First the Pluses           if [Date]=[Start Date] then 1 else 0 end   -- this does noting other than look ate the Data list the I added and joined to your data and put a value of 1 when = to the start date

              The the minuses values      if [Date]=[End Date] then -1 else 0 end

              And then the difference as running totals      RUNNING_SUM(sum([counter plus])) + RUNNING_SUM( sum([counter minus]))  - this last calculation loos at the total running pluses and minuses separately then does the math

               

              The results in table form are

               

               

              In chart form

               

               

              Let me know if this helps

               

              Jim

              • 4. Re: In/Out Inventory Activity
                Dana Chaffin

                This is great - thanks a lot

                 

                Unfortunately, I still have 10.0 (which doesn't have the Edit Join Calculation feature) so I can't test it out personally

                 

                Our company is getting 10.3 soon so I'm eager to try this new feature out

                 

                Marking as Correct Answer (unless you have a 10.0 solution )

                • 5. Re: In/Out Inventory Activity
                  Jim Dehner

                  Thanks

                  That is disturbing - I will spend some time and try to find a workaround and let you know

                  Jim

                  • 6. Re: In/Out Inventory Activity
                    Jim Dehner

                    I'm still looking but I ran across this

                     

                    Re: Date Comparison with Aggregated and Non-Agregated Fields+ Calendat

                     

                    in the response Tom W (he is an ambassador and is really good)  use sql to do something similar

                    I'm not a sql guy but it may help

                     

                    I will continue looking

                     

                    Jim

                    • 7. Re: In/Out Inventory Activity
                      Jim Dehner

                      Dana

                       

                      There is another resource kettan  that you can try

                      He has a wealth of experience that includes data functions

                      He was the source for the original post I sent you and has experience that dates back past t-9

                      Good Luck

                      Jim

                      • 8. Re: In/Out Inventory Activity
                        kettan

                        Method 1  WINDOW CALCULATIONS

                        Have you tried  Showing Records That Fall Within a Period of Time | Tableau Software ?

                        The nice thing with this method is that it doesn't increase the number of underlying rows and (I think) neither need handling of null in end date.

                        OBS. I have never used this method and therefore without knowledge/experience as a helper in this regard.

                         

                        Method 2  PERIOD SPLIT INTO ROWS

                        You could use the technique described in  Split Periods Into Rows Dynamically .

                        Since handling of nulls isn't really included in that document, it is described here:

                         

                        Filter (original):

                        [Period] <= DATEDIFF( 'month', MAX([Cover Start Date],[Period Start]), MIN([Cover End Date],[Period End]) )

                         

                        Filter (plus null handling):

                        [Period] <= DATEDIFF( 'month', MAX([Cover Start Date],[Period Start]), MIN(IFNULL([Cover End Date],[Period End]),[Period End]) )

                         

                        This was a modification of the Filter formula mentioned under appendix at the bottom of mentioned document.

                        An Appendix B is hereby added to Split Periods Into Rows Dynamically  so null handling also is mentioned.

                        • 9. Re: In/Out Inventory Activity
                          Richard Stahl

                          Jim, I am very  interested in your method here as I regularly work with schedule data.  However I am working with 10.2.2 so while I can do cross data base join on calculations, I cannot open you packaged workbook.  Is there any way you could give more detail on the join you do witht he created excel scaffold date sheet please?  Thanks!

                           

                          Richie

                          • 10. Re: In/Out Inventory Activity
                            Jim Dehner

                            Hi Richard

                             

                            This thread is closed but please contact me at marketanalyticsllc@gmail.com and if you can send a sample of your data -

                            I would llike to see what you are doing and better understand your specific need

                             

                            Jim