Add 'Week of' to Custom Hierarchy

[intapiuser]

Introduction

This Post Explains how to create a widget hierarchy that includes ‘week of’  as a member of the hierarchy.  (Calculates first day of that week)

Business Case

As a business Analyst, I would like to drill down in a Bar Chart with Dated Sales Data from year to quarter, month, week and Day.  In addition, in our organization week start on Tuesday.

Steps

1. Create Custom Field based on existing Date field to define ‘week of’ date.  Depending on your approach to calculating week of, use one of the below formulas.

Day of week	Formula
Sunday	AddDays([ShippedDate], Mod((DayOfWeek(adddays([ShippedDate],0))),7) *-1)
Monday	AddDays([ShippedDate], Mod((DayOfWeek(adddays([ShippedDate],-1))),7) *-1)
Tuesday	AddDays([ShippedDate], Mod((DayOfWeek(adddays([ShippedDate],-2))),7) *-1)
Wednesday	AddDays([ShippedDate], Mod((DayOfWeek(adddays([ShippedDate],-3))),7) *-1)
Thursday	AddDays([ShippedDate], Mod((DayOfWeek(adddays([ShippedDate],-4))),7) *-1)
Friday	AddDays([ShippedDate], Mod((DayOfWeek(adddays([ShippedDate],-5))),7) *-1)
Saturday	AddDays([ShippedDate], Mod((DayOfWeek(adddays([ShippedDate],-6))),7) *-1)

      2.  Rebuild Elasticube

      3.  Define Hierarchy using the shipped date for year, quarter, month and day, use the new ‘week of’ field created for the week field.

      4.  Hierarchy now will be available to be used in widgets.