3 Replies Latest reply on Jul 27, 2016 12:12 PM by Andrew Watson

    Counting # days for any chosen period of time

    Teralyn Dorst

      I have an equation that divides a sum of inventory by the number of days that are being summed to find the average inventory for that period of time. The user is able to break down the time period by quarter>month>week>day and by different classes shop>department>class. I created an equation to count to the number of days by returning a "1" if there is a value present under the label "day."


      Ex:     Day          Day Count

                1               1

                2               1

                3               1

                4               1

                ...7            1


      So that the total number of days for each week is 7. Ideally, as I roll up to month the day count would become ~28-31 (some weeks have more or less than 7 days) and at quarter it would become ~90 days. However, this will only work (most of the time) at the class level and rolled up higher (ex: to department) it sums all of the day counts for each class so that the month day count becomes ridiculously high rather than 28-31, causing an inaccurate portrayal of the average inventory.


      Any thoughts on how to combat this? I'm new to Tableau and I'm thinking I'm going to have to do more than just a day count equation to fix this, but it would be great if I didn't have to change the other equations because they are still correct, just the Average Inventory and Day Count are incorrect.