3 Replies Latest reply on Dec 31, 2018 1:34 AM by Simon Runc

    To Convert Date dimension  Days to months

    john lane

      Hello Everyone,

       

      I got a requirement regarding the Date dimension as if i used the Date as filter and selected the range of dates i at  filter reflects Date as slider on worksheet. If  i select the start date and end date, if between two dates is less than 30 days  it should show the days but if the start and end date selection are greater than 30 days it should show month level date like Feb,Mar.

       

      Please help me out.

        • 1. Re: To Convert Date dimension  Days to months
          Simon Runc

          hi John,

           

          There are a few ways we could do this. Here is a simple one

           

          I created a display level for date depending on the number of days in the filter

          [Order Date Level]

          DATE(IF DATEDIFF('day',{MIN([Order Date])},{MAX([Order Date])}) > 30 THEN

          DATETRUNC('month',[Order Date])

          ELSE [Order Date]

          END)

           

          I then use this on my axis. You'll also need to have to add the date filter to be a context filter (right click on filter and select add to context) as I've used LoDs, which are computed before any regular filters are applied.

           

          One issue with this is that you can't automatically change the date format (so even at month it will show 01/01/2013, 01/02/2013...etc.). To get round this you could create 2 sheets, one for Day Level and one for Month level (with the correct formatting and use the test part)

          DATEDIFF('day',{MIN([Order Date])},{MAX([Order Date])}) > 30

           

          to trigger a sheet swap. I've also done this version.

           

          Hope that helps

          • 2. Re: To Convert Date dimension  Days to months
            john lane

            Hi Simon,

             

            Thank you for the given calculation, could you please share any sample workbook it helps me a lot.

            • 3. Re: To Convert Date dimension  Days to months
              Simon Runc

              hi John,

               

              Glad it helped, I attached an example workbook to my answer. You should be able to see it at the bottom of the response