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

Counting # days for any chosen period of time

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.

• 1. Re: Counting # days for any chosen period of time

Can you do a COUNTD([Day]) instead of a sum of day count? That all depends on how your data is structured.

• 2. Re: Counting # days for any chosen period of time

I have tried this but it doesn't work for my data; as I roll up the time periods it will keep the same count rather than aggregating to give an accurate reflection of the # days in a month, quarter, etc. I'll try to post my workbook for further reference.

• 3. Re: Counting # days for any chosen period of time

Ok - another stab in the dark would be to alter your Day Count formula to be: WINDOW_AVG(SUM([Day Count])). Again, the success is dependent on your data structure.