7 Replies Latest reply on Aug 17, 2016 6:27 AM by Michael Hesser

    Counting days with events

    Ramandeep Khangura

      Hello all, I am pretty new to Tableau and in need for help.

      I need to calculate how many times stock went lower than the Reorder point.

      If Reorder point > stock Available then “Out of Stock”

      “OUT OF STOCK” would be a calculated field

      I am able to calculate if the status is “OUT OF STOCK” or not.

      I am not able to calculate: If status stays “OUT OF STOCK” for up to 14 days (2 weeks) then count as 1 event. Like in the following example it would be considered 2 OUT OF STOCK event

      Thanks you for your time and I will really appreciate any help with this problem.

      Raman

       

       

       

      Sample Data

      Date

      Material Name

      Stock Available

      Reorder point

      OUT OF STOCK(Y/N)

      8/11/2016

      A123

      150

      200

      Y

      8/12/2016

      A123

      150

      200

      Y

      8/15/2016

      A123

      150

      200

      Y

      8/16/2016

      A123

      150

      200

      Y

      8/17/2016

      A123

      300

      200

      N

      8/18/2016

      A123

      180

      200

      Y

      8/19/2016

      A123

      180

      200

      Y

      8/20/2016

      A123

      180

      200

      Y

      8/23/2016

      A123

      180

      200

      Y

      8/24/2016

      A123

      180

      200

      Y

      8/25/2016

      A123

      180

      200

      Y

      8/26/2016

      A123

      180

      200

      Y

      8/29/2016

      A123

      180

      200

      Y

      8/30/2016

      A123

      250

      200

      N

        • 1. Re: Counting days with events
          lei.chen.0

          Quite difficult

           

          Managed to create a partition flag for each out of stock period, but failed to calculate the period length for each period.

          This is as far as I can go, and expecting for brilliant solutions.

           

           

          Regards

          • 2. Re: Counting days with events
            Ramandeep Khangura

            Thanks Lei for the effort. I was able to count the number of times it is OUT OF STOCK. I see possibility in your approach if the counter can be reset to 1 after it notices a IN STOCK status ot after 14 days. Can you please share how did you come up with this count?

             

            Thanks

            • 3. Re: Counting days with events
              Michael Hesser

              If you can make this partition, can you make a LOD calculation on it?

               

              If you are taking daily measurements (not taking into account weekends and so on) :

               

              Days Out Of Stock by Partition

              {FIXED [Partition]:count([Date]}

               

              If you're not taking into account weekends, etc, you should be able to subtract the MIN([Date]) from the MAX([Date])+1 to get days.

               

              Then a simple calc can help you determine if there is an EVENT or not:

              Event

              IF [Days Out Of Stock by Partition] >= 10 then "Event" else "Not critical" end

              //Here 10 is equivalent to 10 business days, of 14 days. You can modify this to 14 if you're subtracting min/max dates.

               

              Naturally, this means a single Event could be 14 days or 14 years-- it wouldn't ring the alarm for every day after the the 14.

               

              Maybe this is the direction you were heading? --Michael

              • 4. Re: Counting days with events
                Ramandeep Khangura

                Hello Michael, The data I'm using MS Access, which did not support the Fixed function.

                My apologies, I should have mentioned it in my question.

                I am also looking at it like this:

                 

                If STOCK OUT =1 day then check next day,

                if next day is stock out then count as only one day and keep doing this until 14 days.

                Reset the counter after 14 days.

                 

                Something like this...any thoughts?

                 

                Regards

                Raman

                • 5. Re: Counting days with events
                  Michael Hesser

                  This is a little tricky without seeing the data (could you publish a .twbx?).

                   

                  What about creating an index or rank by partition? You could count it and compare int([Index Count]/14) to [Index Count]/14. If they are equal, you'll know you've found the 14th occurrence per partition.

                   

                  I'm probably over simplifying this since some calculations blow up when you try to aggregate them... but it might just work. Thoughts? --Michael

                  • 6. Re: Counting days with events
                    lei.chen.0

                    Hello Ramandeep,

                     

                    I see possibility in your approach if the counter can be reset to 1 after it notices a IN STOCK status ot after 14 days. Can you please share how did you come up with this count?

                     

                    This is right the point where I am stuck at.

                     

                    Please find the details in the attached workbook.

                     

                    I have a question about logical.

                     

                    The dates, at least in your sample data, are not continuous.

                    For example, there's not 8/13 and 8/14 (maybe because it's weekend?)

                    When counting the days of "OUT OF STOCK", should the missing dates be counted?

                     

                    This is a matter of,

                    1) counting the number of "Y"s in the partition, or

                    2) finding the date difference from the first out of stock date in the partition.

                     

                     

                    Regards.

                    1 of 1 people found this helpful
                    • 7. Re: Counting days with events
                      Michael Hesser

                      This is great work, Lei Chen

                       

                      It looks like my suggested method won't work, for reasons Bora Beran outlined in another post:

                       

                      "LOD expressions are done in the database, table calculations are computed locally on your computer. That's the reason why you can't mix the two the way you tried. It would work the other way e.g. you could consume the output of LOD calcs inside a table calculation.

                       

                      You could do all of this as table calculations though. You could use window_sum without a moving window at group level as well. If you set partition setting to Group in Edit Table Calculation > advanced... dialog. Your window becomes Group so you will get the sum for the group."

                       

                      He might be a good resource, though.

                       

                      Another thought: since this is in Access, are you able to add calculations that would count occurrences and flag these events? It's fairly straight forward in Excel. Here's wishing you good luck! --Michael

                      1 of 1 people found this helpful