3 Replies Latest reply on Dec 6, 2018 11:39 AM by Alexander Mou

    Count days even if your data is missing days for the dimension

    Lynn Carlson

      In the example below how would I created a count of business days to be used across all dimension even if the dimension doesn't have a record for the day.  It would also need to be flexible to work based on the date range of the view.

       

         

      I can get dimension 12345 to return the correct number of days by summing INT({Fixed [Row_Date]:MIN([Is Business Day])}).  However for dimension 67890 it returns 16 because there are missing days.  I have no way of building the input to include the missing days for 67890.

      Dimension
      DateBusiness Day1234567890
      1-Jul000
      2-Jul11
      3-Jul111
      4-Jul000
      5-Jul111
      6-Jul111
      7-Jul000
      8-Jul000
      9-Jul11
      10-Jul111
      11-Jul111
      12-Jul111
      13-Jul111
      14-Jul000
      15-Jul000
      16-Jul11
      17-Jul111
      18-Jul111
      19-Jul111
      20-Jul111
      21-Jul000
      22-Jul000
      23-Jul11
      24-Jul111
      25-Jul111
      26-Jul111
      27-Jul111
      28-Jul000
      29-Jul000
      30-Jul11
      31-Jul111
      Total212116
        • 1. Re: Count days even if your data is missing days for the dimension
          Alexander Mou

          How about create a new column [67890+] like

           

          if 67890=Null then [Business Day] else [67890] end

           

          then counting the days using this new column.

          • 2. Re: Count days even if your data is missing days for the dimension
            Lynn Carlson

            Thanks for the suggestion but that wouldn't work in this case.  My illustration was miss leading.

             

            The data coming in looks like the below.  For part 67890 there are no records for some of the business days yet I still need to include those days in calculations that use count of business days.  So regardless of how many records come in for each part the calculation needs to use the same number of business days across all parts.

               

            PartDateBusiness Day
            123451-Jul0
            123452-Jul1
            123453-Jul1
            123454-Jul0
            123455-Jul1
            123456-Jul1
            123457-Jul0
            123458-Jul0
            123459-Jul1
            1234510-Jul1
            1234511-Jul1
            1234512-Jul1
            1234513-Jul1
            1234514-Jul0
            1234515-Jul0
            1234516-Jul1
            1234517-Jul1
            1234518-Jul1
            1234519-Jul1
            1234520-Jul1
            1234521-Jul0
            1234522-Jul0
            1234523-Jul1
            1234524-Jul1
            1234525-Jul1
            1234526-Jul1
            1234527-Jul1
            1234528-Jul0
            1234529-Jul0
            1234530-Jul1
            1234531-Jul1
            678903-Jul1
            678905-Jul1
            678906-Jul1
            6789010-Jul1
            6789011-Jul1
            6789012-Jul1
            6789013-Jul1
            6789017-Jul1
            6789018-Jul1
            6789019-Jul1
            6789020-Jul1
            6789024-Jul1
            6789025-Jul1
            6789026-Jul1
            6789027-Jul1
            6789031-Jul1
            • 3. Re: Count days even if your data is missing days for the dimension
              Alexander Mou

              Seems you already know

              1) the start day and the end day of your period of interest

              2) the business days in between.

               

              There is no need for records. With zero records, we can still figure out the number of business days.

              Right?