3 Replies Latest reply on Dec 27, 2018 12:31 PM by Brian Fase

    Fixed CountDistict Date for Daily Average

    Brian  Fase


      I have a data set that is pulling the last 12 calendar days of dispensing data by a zone.  On some business days a zone may not dispense a product so the district count of day for that zone is less than the total district count of business days for the entire data set (for example 6 out of 8 business days).  I want to calculate the average amount dispensed by each zone over the total 8 business days however when I setup my table and calculated field it only gives the average for the business days that the zone is dispensing not the entire 8 business days (i.e. 100 dispensed/ 6 days instead of 100 dispensed/ 8 days.  I have tired to use "Fixed" on the count distinct days in the calculated field however it just gives me the same 6 business days instead of 8.  Since the data pull in a rolling 12 calendar days I can't use a fixed denominator because the number of business days in the data set will not be the same based on weekends, holiday, overtime ect.


      I have recreated the issue with the superstore data as an example where it shows sales by State over the last month.  I tired to get it to show average sales per day for each state in the month but again it only divides the sales by the number of days that had a sale not the entire 30 days in the month.


      Thanks for any insight you can provide.


        • 1. Re: Fixed CountDistict Date for Daily Average
          Joe Oppelt

          I'm opening this now.  Before I dig in, help me understand something better.


          Some zones have the full set of business days, and some do not.  Is that correct?  (Specifically, could there be a 12-day period when NO zones have the full set of business days?)

          • 2. Re: Fixed CountDistict Date for Daily Average
            Joe Oppelt

            OK, so I have this open.  You have the filter set to "this month", and you want to arrive at a total of 30 days to divide things by.


            Calif has the largest number of days at 25.  But even Calif doesn't have sales in all days.


            When you have your actual set of data, it will span only 12 calendar days, is that correct?  Will any zone ever have transactions on the non-business days?


            In the attached I made Calculation1 on Sheet 2 (in the title) that tells you how many days in the filtered range have actual data.  I get 29.  Nobody has any transactions on 12/12.


            If none of your zones will ever have transactions on non-business days, you can do the same sort of calc to get your denominator based on the given data set.

            • 3. Re: Fixed CountDistict Date for Daily Average
              Brian  Fase

              Thanks for the help.


              This did the trick.  I created a calculated field called "Business Days"  and put {Fixed:COUNTD([Production Day])}.  Then created another calculated filed with SUM ([Dispensed])/ Sum ([Business Days]) to get the dispensed average per day.


              My previous calculated field was setup with {Fixed [Production Day] : COUNTD([Production Day])} which didn't give me the correct total business days.