KNOWLEDGE BASE

Create Adjustable-Size Date "Bins"


Published: 22 Jun 2017
Last Modified Date: 22 Jun 2017

Question

How to create adjustable-size date "bins"?

Answer

It is possible to create a calculation to create date "bins" by a selectable date size, using the following:

1. Level of Detail Expressions (LODs)
An LOD makes it possible find the earliest date in the data. In the sample workbook, the calculation is:
{FIXED : MIN([Order Date])}

2. DATEDIFF
By taking the difference in days between any given date and the first date in the data, a number is returned that can be divided by the bin size, as set by a parameter.

3. FLOOR
The FLOOR function rounds down to the nearest integer, which results in matching whole number values for every n dates, where n is the bin size.

The resulting calculation is:
FLOOR( DATEDIFF('day', {FIXED : MIN([Order Date])}, [Order Date])/[Bin Size] )

 

Additional Information

Considerations:
  1. The first "bin" value using the above calculation will be 0. If 1 is preferred, add 1 to the calculation:
    FLOOR( DATEDIFF('day', {FIXED : MIN([Order Date])}, [Order Date])/[Bin Size] ) + 1
  2. FIXED LODs are evaluated before dimension filters or measure filters are applied, which means that filtering out the first date in the data will not prevent it from being the result of the LOD. To filter the date out from the LOD, such a dimension filter would need to be added to context, or an EXCLUDE LOD would need to be used instead.
  3. If preferred, a table calculation may be used instead of a FIXED LOD:
  4. WINDOW_MIN(MIN([Order Date]))
    Table calculations evaluate based on the data in the view, after filters have been applied.

 
Did this article resolve the issue?