2 Replies Latest reply on Jan 8, 2019 10:16 AM by Naveen B

    How to get Week Number and Year (Week 42, 2018) with option to refresh to latest week?

    Owais Shaikh

      Hello Everyone,

       

      I have a date column in my data that is represented as  31/12/2018 00:00:00. I have a week number filter which I want to associate with a year as mentioned below.

       

      • Week 51, 2018
      • Week 52, 2018
      • Week 01, 2019
      • Week 02, 2019

       

      As of now, there is no direct discrete DateTime function available to get these values. I have written a calculated field to achieve this.

      "Week" + Datevalue("Week",Date) + Datevalue("Year", Date)      (Roughly)

       

      But when I put this into the filter, I am losing an option to refresh to latest week since its no more longer a DateTime function.

       

      (Not from my Dashboard)

      filterpreset-discretedate-2.png

       

      I want a week number,year filter with this option or my dashboard get refreshed to the latest week. Any help would be appreciated.

        • 1. Re: How to get Week Number and Year (Week 42, 2018) with option to refresh to latest week?
          maneesh.gaddam

          Owais,

           

          As a work around, you achieve it using parameters. Create a String parameter as 'Date selection' with values current week and History.

           

          Write a calculated field as

          CASE [Date Selection]

          WHEN 'Current Week' then [Date Field]={Fixed: MAX([Date Field])}

          WHEN 'History' then [Date Field]>={Fixed: MIN([Date Field])} and [Date Field]<={Fixed: MAX([Date Field])}

          END

           

          Put this calculated field into filter and select TRUE. Also put your date field into filter and select All. Also show only relevant values.

           

          Now let the default selection for Parameter be 'Current week'.

           

          So, when the dashboard gets updated with new data, based on the case statement current week condition, it picks up latest week data and the [date field] also shows the latest week. When we flip Parameter to 'history', based on the condition again, it will let user select the date ranges.

          • 2. Re: How to get Week Number and Year (Week 42, 2018) with option to refresh to latest week?
            Naveen B

            Hi Owais,

             

            Right Click on the Date Field --> Drag it to Filter --> Select Week Number

             

            you will get the required O/P along with the Filter to latest date option.

             

            Hope this helps,

             

            Plz mark this answer as correct or helpful to close the thread

             

            BR,

            NB