1 Reply Latest reply on Aug 8, 2016 11:35 AM by Benjamin Greene

    counting distinct days in a filtered date range

    Dan Gerena

      I have transactional data. Hundreds of records per day. I'm trying to allow the user to specify a date range (say 8/8/2016 thru 8/14/2016) and I want a field that counts the days in that range (I.e. 7).

       

      However I want the user to also specify the exclusion via a filter if certain weekdays, like "exclude Wednesday's".

       

      Thus I want the result to instead be 6 not 7 in that example. Thus I can't simply subtract the min date from the max date.

       

      can level of detail help me accomplish this?

        • 1. Re: counting distinct days in a filtered date range
          Benjamin Greene

          I think this packaged workbook contains what you are looking for. Here's how I made it.

           

          1. Create Start Date and End Date parameters based on the date field. Right click both of them and click Show Parameter Control.

          2. Create a Date Range calculated field with the following syntax:

          [Date]<=[End Date]

          AND [Date]>=[Start Date]

          3. Drag Date Range to the Filters card and select True.

          4. Create a Days to Exclude calculated field with the following syntax:

          DATENAME('weekday', [Date])

          5. Right click Days to Exclude and select Show Filter.

          6. Create a Number of Days calculated field with the following syntax:

          COUNTD(STR(DAY([Date]))+STR(MONTH([Date]))+STR(YEAR([Date])))

          7. Drag Number of Days to Text.

           

          Let me know if this works for you.

          1 of 1 people found this helpful