Introduction

This article explains how to create a report to show number of items in a view of SharePoint list corresponding to week number of a date field in list. Please follow the below steps to configure this report using the Crow Canyon Report App.

 

Create a calculated column of type Text called “Week Number” on ‘Tickets’ list, using the following formula

Go to list settings and Create a new Calculated Column with formula:

=IF(INT(([Created]-DATE(YEAR([Created]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Created]),1,1)-1),”d”)))/7)=0,52,INT(([Created]-DATE(YEAR([Created]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Created]),1,1)-1),”d”)))/7))

Above formula will get the week number (1 to 52). If you want to prefix the week number with a text say ‘Week-‘ then use the below formula instead:

=CONCATENATE(“Week-“,TEXT(IF(INT((Created-DATE(YEAR(Created),1,1)+(TEXT(WEEKDAY(DATE(YEAR(Created),1,1)-1),”d”)))/7)=0,52,INT((Created-DATE(YEAR(Created),1,1)+(TEXT(WEEKDAY(DATE(YEAR(Created),1,1)-1),”d”)))/7)),”0″))

You can replace the date column in above formula by replacing all instances of ‘Created’ with some other date field name.

 

Save it as a ‘Single Line of Text’ column.

 

Create a view on the list to get items from a specific period

 

Here we are creating a View called ‘Tickets by Year’ on ‘Tickets’ list to get all items created in year 2016. Fiter the Items in the View as shown in the below picture.

 

 

Create Report to show items by Week

Navigate to Crow Canyon Reports App. You can click ‘Manage Reports’ in Crow Canyon Report Centre. You can also navigate to the Site Contents and then click ‘Crow Canyon Reports App’.

Configure the Report as per the settings shown below: