How to retrieve previous year’s march data for a particular measure?


For example: if current year is 2016 and we need to retrieve data for March-2015. In the coming year i.e 2017, we would need March-2016 data and so on.

Solution: This can be achieved by using the relative time series feature and filter.

  • Create cross tab with two measure as shown below,

  • Rename the column headers to LY-March for [GrossSales] & Last Three Months for [GrossSales_1]
  • Add relative time series to the measure LY-March [GrossSales] as Year-1 as shown below.

  • Similarly, add relative time series to the measure Last Three Months [GrossSales_1] as Year, Month, Month-1, Month-2
  • Create a filter to get only March month’s data. (Here filter condition to be created is Month = 12 for using financial year which starts from April.)
  • Now click on “Apply condition to below columns” and apply this filter condition to required column (Here filter is applied on LY-March [GrossSalescolumn).

  • The resultant crosstab would be.

Note: This article is based on ElegantJ BI Version 4.3. This may or may not be relevant to the ElegantJ BI version you may be using.