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 [GrossSales] column).
- 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.