1 Reply Latest reply on Sep 18, 2018 3:01 PM by Ivan Young

    Current quarter up to the current completed week

    Tony Smith

      Hi All,

       

      Can someone please help me with the query below?

       

      I have attached a current quarter report to this query. I have manually filter the wk_in_fin_year on the report, I need a formula that can do this for me.

       

      The four quarters based on the BBCA fiscal year are as follows:

       

       

      March to May - Q1

      June to August  - Q2

      September to November - Q3

      December to February - Q4

       

       

      The report shows the current quarter which is  Q3 (Sept 2018 to Nov 2018).  The Q3 data also show sales figures up to the current completed week.  There is a Complete week calendar in the attached report.  EG for the date 17th September 2018, this will be week 28.  If the report is run on 23rd September, the completed week will be week 29.

       

      I only want to see the sales for the current quarter up to the current completed week.(starting from the 1st of the quarter)

       

      To determine the current BBCA completed week, I have added a calendar to the report.

       

      Thanks

       

      Regards

      Tony

        • 1. Re: Current quarter up to the current completed week
          Ivan Young

          HI Tony,

          I'm still on 10.2 so I didn't download your workbook but you can create a filter like the one below which should return the records you are looking for.  Let me know if you have any questions.

           

          Regards,

          Ivan

           

           

           

          DATEPART('quarter', [Your Date]) = DATEPART('quarter', TODAY())

          and

          DATEPART('year', [Your Date]) = DATEPART('year', TODAY())

          and

          DATETRUNC('week', [Your Date]) < DATETRUNC('week', TODAY())