6 Replies Latest reply on Aug 8, 2016 2:44 AM by harris.tsr

    Custom Week

    harris.tsr

      Hi all,

       

      I'm working on a dashboard that another user built and there is a filed where we use the source data to create a custom week view with the following calculated field:

       

      if [YEAR] = 'FY17' and DATEPART("year",[L_DATE] ) = 2017 THEN "W"  + STR(DATEPART("week",[L_DATE] )+22)

      ELSE IF [YEAR] = 'FY17' and DATEPART("year",[L_DATE]  ) = 2016 THEN "W" + STR(DATEPART("week",[L_DATE] )-30)

       

      So, basically, I try to figure out what these +22 and -30 mean? It would be great if someone could advice on that.

       

      Thanks in advance

        • 1. Re: Custom Week
          Steve Taylor

          Hello

           

          The DATEPART('week', [L-Date]) section of your calculation returns the week number of your [L-Date] field. What this calculation above is saying is if the year of [L-Date] is 2017 then create a string field that begins with "W" then has the week number of the date + 22, which means they're adding 22 weeks onto whatever week number your L-Date is.

           

          Again if the year of [L-Date] is 2016 then create a string field that begins with "W" then take the week number of the date and remove 30 weeks from it.

           

          Does that make sense? I'm not sure WHY you'd add 22 weeks onto a week number in 2017 or remove 30 from one in 2016 but that's what this calculation does.

           

          Steve

          • 2. Re: Custom Week
            Carl Slifer

            Hi Harris,

             

            I can tell you what the values are doing but I will have to guess why it is occurring.  Your predecessor is creating a new column that will spit out W16, W29, W38, etc. To use as a header I believe and to sort dates into weeks based on your chosen financial year periods. It appears that the +22 adds 22 weeks to the number. This is because if something occurred in the first week of the calendar year 2017 it actually was the 23rd week of your financial calendar. Likewise is something occurred in the last week of 2016 it occurs in week 22 of your fiscal year instead of week 52 of the calendar year.

             

            Cheers!

            Carl Slifer

            InterWorks

            • 3. Re: Custom Week
              Steve Taylor

              Should've said, week number is 1-52 beginning at the start of your year (whichever that is set to in your datasource, could be 1st of Jan or maybe a fiscal year start date).

              • 4. Re: Custom Week
                Simon Runc

                Interesting problem!!

                 

                So the First statement is testing if [Year] = "FY17" and the Year of the actual date field [L_DATE] is 2017. If it is it takes the [L_DATE], adds 22 days to it, and returns the Week Number of that date.

                 

                 

                the second one works the same but if [YEAR] = "FY17" and [L_DATE] = 2016 THEN minus 30 days from L_DATE and take the Week Number

                 

                 

                So this looks like a formula to return dates in Financial Weeks (using the actual calendar year and FY to calculate the Financial Week)

                 

                hopefully that makes sense in your context!

                 

                 

                              

                L_DATEYEARL_DATE + 22Week Number of   L_DATE+22
                01/01/2017FY201723/01/20174
                • 5. Re: Custom Week
                  Simon Runc

                  Apologies Harris...I read it as DATETRUNC not DATEPART...so answers from Steve and Carl are correct!!...I'll put it down to being Monday morning!!

                  • 6. Re: Custom Week
                    harris.tsr

                    Thank you Steve, Simon, Carl. As I guessed, it adds/removes 22/30 weeks, still not sure why these specific numbers, but I noticed I need to remove 31 weeks when the L_Date is 2016 and the Year = 2017 because 2016 was a leap year. In this way, I can viz the previous week which was the week 1 of fy17 as "W1".