6 Replies Latest reply on Aug 1, 2018 5:04 AM by Tony Smith

# Current quarter up to the current completed week

Hi All,

I have attached a quarterly report to this query.  At the moment the quarter to quarter report works for Q1, Q3 and Q4.  However for Q2 which is the current quarter accord to the BBCA calendar, it gives me all the sales values for Q2.  I only want to see the sales for the current quarter up to the current completed week.

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

Using the BBCA week in fin year field the current week is 1921, however I want to see data for a complete week which is 1920 and not week 1921 which has not yet completed.  If I run the report on the 29th July 2018 when we will have completed week 1921, the current week will be 1921.

Thanks

Regards

Tony

• ###### 2. Re: Current quarter up to the current completed week

Hi Kumar,

Thank you for helping me out with this query. I have gone through all the links sent to me but I still need help with the report.

What I need is someone to help me look in the report.

As I mentioned before Q1, Q2 and Q3 are working as expected. But the current quarter is returning all the sales for this quarter instead of up to the current completed week. The current completed BBCA week in fin year is 1921.

Regards

Tony

• ###### 3. Re: Current quarter up to the current completed week

Tony,

I don't think I quite got there, but please see if the below

could give some ideas.

Like what you had set up, I made a [Current BBCA Week]:

{ FIXED : MAX (IF [Today Parameter]=[Report Date] THEN [BBCA Week In Fin Year] END)}

// I used a parameter to try different todays, but you can replace with TODAY(), as you had earlier

Then I used this to determine if the current week was complete:

IF

{ FIXED :

COUNTD( IF [Report Date]<=[Today Parameter] AND [BBCA Week In Fin Year]=[Current BBCA Week]

THEN [Report Date] END) }

= 7 THEN "Week Complete" END

Lastly, I created a Boolean as to whether to include the week or not:

( [BBCA Week In Fin Year]=[Current BBCA Week]

AND [Current Week Complete]="Week Complete" )

OR [BBCA Week In Fin Year]<[Current BBCA Week]

• ###### 4. Re: Current quarter up to the current completed week

Hi swaroop,

Thank you for help on this query.  I have looked at your solution and l have tried everything I know to resolve this issue but still I can't  resolve it.  Hopefully, I'll will get someone on this forum who can resolve this query for me.

Regards

Tony

• ###### 5. Re: Current quarter up to the current completed week

Hi Tony,

Please see the attached workbook. In your data source, I changed the week start to be sunday at the data source:

Then I create my calculation to be used as a filter:

Then set it to 'Show' in the filters shelf. So now it goes from Week 30 in Q3 2018, then to Q4 2018 week 40:

I've also compared it to your BBCA Week in Fin Year field and it's coming up as 1921:

Thanks and please see attached workbook.

Thanks,

Mavis

• ###### 6. Re: Current quarter up to the current completed week

Hi Mavis,

One thing I forgot to mention is that the fiscal date starts in March every year.  I want to display 4 quarters based on the BBCA fiscal year and BBCA fiscal period.

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

Is it possible to amend the code to start in March instead of January?

The report should show the current quarter which is  Q2 (Jun 2018 to Aug 2018).  It should also show Q1 for this fiscal year (Mar 2018 to May 2018), as well as  Q3 and Q4 of the previous fiscal year (Sept 2017 to Nov 2017 and Dec 2017 to Feb 2018).

I also want to compare the data from the quarters above with data from the corresponding quarters from the previous fiscal years - Q1 (Mar 2017 to May 2017), Q2 (Jun 2017 to Aug 2017), Q3 (Sept 2016 to Nov 2016) and Q4 (Dec 2016 to Feb 2017)

Thanks

Regards

Tony