2 Replies Latest reply: Mar 25, 2014 10:02 AM by Colin Devon

# Point in Time Analysis - Assigning verbalised labels for reporting year

Wanting to do a table that has the current reporting year and the previous N years.

Very Basic Straight Table

2011/122012/132013/14
1,5001,6001,450

Not a problem for the definition but with the label I can only think of using the following below which does but I was wondering if there was an easier or more elegant way.

Variables

vDaytoDateReportingYear=DayNumberOfYear(Max({<Dimension_Category-={0} > } Date), 4)

vCurrentReportingYear=Max({<Dimension_Category-={0} > } Reporting_Year)

Expresion Definition

=SUM({< DayOfReportingYear={"<=\$(vDaytoDateReportingYear)"}, Reporting_Year={"\$(=vCurrentReportingYear-3)"}, Fact_Count)

Expression Label

=FieldValue('Reporting_Year_Verbalised', FieldIndex('Reporting_Year_Verbalised', vCurrentReportingYear-3))

where

Reporting_Year = 2014

Reporting_Year_Verbalised = 2013/14

• ###### Re: Point in Time Analysis - Assigning verbalised labels for reporting year

Hi.

=\$(=Reporting_Year-1) & '/' & right(\$(Reporting_Year), 2)

• ###### Re: Point in Time Analysis - Assigning verbalised labels for reporting year

Thanks WhiteLine.

One issue would be that for the 3 labels above (2011/12, 2012/13, 2013/14) I've have to do

• =\$(=Reporting_Year-3) & '/' & right(\$(Reporting_Year-2), 2)
• =\$(=Reporting_Year-2) & '/' & right(\$(Reporting_Year-1), 2)
• =\$(=Reporting_Year-1) & '/' & right(\$(Reporting_Year), 2)

I was hoping something like lookup(fieldname, matchfieldname, matchfieldvalue [, tablename]) which I saw in inter record functions could be used but I don't seem to have it as an option. Guessing it is only available in Script load.