Ad Hoc View – Calculated Fields and Measures – Date Examples

  1. Home
  2. Ad Hoc Views
  3. Ad Hoc View – Calculated Fields and Measures – Date Examples
  1. Home
  2. Examples
  3. Ad Hoc View – Calculated Fields and Measures – Date Examples

Ad Hoc Views allow you to visualise and analyse the data you capture for your organisation.

Ad Hoc Views can be viewed in the Community BI system or in reports and dashboard.

Calculated Fields and Measures allow you to calculate additional information from your captured data.

When you have data with a date field, you may wish to group records by Financial or Calendar years.

 

 

Instructions

Step 1.  Open or create a new Ad Hoc View (refer to Creating Ad Hoc Views for further information)

Step 2. Refer to Calculated Fields and Measures for detailed instructions to create your measure

Step 3. Refer to the examples below for Measure names and formulas

Step 4. Add the calculated field to your Ad Hoc View

 

 

To show the measures grouped by Financial year:

Measure Name = Financial Year
Formula = CaseWhen(MonthNumber("Date") <= 6, Concatenate('FY', Year("Date") - 1, '/', Year("Date")), 
Concatenate('FY', Year("Date"), '/', Year("Date") + 1))

Note - where the example above uses "Date" you will need to substitute 
the appropriate date field from your database.

Session Count by Financial Year

 

 

To show the measures grouped by Financial years in quarters:

Measure Name = Financial Year Quarter
Formula =CaseWhen(MonthNumber("Date") <= 3, Concatenate('FY', Year("Date") - 1, '/', Year("Date"), 
' Q3'), CaseWhen(MonthNumber("Date") <= 6, Concatenate('FY', Year("Date") - 1, '/', Year("Date"), 
' Q4'), CaseWhen(MonthNumber("Date") <= 9, Concatenate('FY', Year("Date"), '/', Year("Date") + 1, ' Q1'), 
Concatenate('FY', Year("Date"), '/', Year("Date") + 1, ' Q2'))))

 

Note - where the example above uses "Date" you will need to substitute 
the appropriate date field from your database.

Session Count by Financial Year Quarter


 

To show the measures grouped by Calendar years in quarters:

Measure Name = Calendar Year Quarter
Formula =CaseWhen(MonthNumber("Date") <= 3, Concatenate('Q1/', Year("Date")), 
CaseWhen(MonthNumber("Date") <= 6, Concatenate('Q2/', Year("Date")), CaseWhen(MonthNumber("Date") <= 9, 
Concatenate('Q3/', Year("Date")), Concatenate('Q4/', Year("Date")))))

Note - where the example above uses "Date" you will need to substitute 
the appropriate date field from your database.

Session Count by Calendar Year Quarter


 

 

To show the measures grouped by Calendar years in quarters, sorted by the year, then quarter:

Measure Name = Calendar Year Quarter (Sortable year first)
Formula =CaseWhen(MonthNumber("Date") <= 3, Concatenate(Year("Date"), '/Q1'), 
CaseWhen(MonthNumber("Date") <= 6, Concatenate(Year("Date"), '/Q2'), CaseWhen(MonthNumber("Date") <= 9, 
Concatenate(Year("Date"), '/Q3'), Concatenate(Year("Date"), '/Q4'))))

 

Note - where the example above uses "Date" you will need to substitute 
the appropriate date field from your database.

Session Count by Calendar Year Quarter


 

 

 

To show the measures grouped by Calendar years in halves:

Measure Name = Calendar Year Half
Formula =CaseWhen(MonthNumber("Date") <= 6, Concatenate('Jan-Jun/', Year("Date")), 
Concatenate('Jul-Dec/', Year("Date")))

Note - where the example above uses "Date" you will need to substitute 
the appropriate date field from your database.

 

Session Count by Calendar Year Half


 

 

To show the measures grouped by Calendar years in halves, sorted by the year, then half:

Measure Name = Calendar Year Half (Sortable year first)
Formula = CaseWhen(MonthNumber("Date") <= 6, Concatenate(Year("Date"), ' Jan-Jun'), 
Concatenate(Year("Date"), ' Jul-Dec'))

Note - where the example above uses "Date" you will need to substitute 
the appropriate date field from your database.

Session Count by Calendar Year Half

 

Further Information

For further information on Ad Hoc Views, refer to the following articles:

Related Articles