Calculating Week Number Of Quarter

[intapiuser]

Introduction

This Post Explains how to create a new column that will calculate the week number of a Quarter.

Business Case

In many cases Business Analyst would like to track trends over time aggregated by quarters but broken down to the week within each quarter. 

Steps

1. Create Custom Field based on an existing Date field
2. Enter the following code in the formula editor:

Case
  When getquarter(Date-Field) = 1 and weekofyear(Date-Field) > 13 then 1
  When getquarter(Date-Field) = 1 then weekofyear(Date-Field)
  When getquarter(Date-Field) = 2 then weekofyear(Date-Field) - 13
  When getquarter(Date-Field) = 3 then weekofyear(Date-Field) - 26
  When getquarter(Date-Field) = 4 then weekofyear(Date-Field) - 39
End

1. Rebuild Elasticube
2. Create a widget where the data is aggregated by quarter and broken down to weeks within a quarter as seen below: