topic Point in Time Analysis - Assigning verbalised labels for reporting year in QlikView

Point in Time Analysis - Assigning verbalised labels for reporting year

Mon, 24 Mar 2014 12:10:30 GMT

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

Very Basic Straight Table

2011/12	2012/13	2013/14
1,500	1,600	1,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

whiteline — Mon, 24 Mar 2014 20:33:48 GMT

Hi.

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

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

Tue, 25 Mar 2014 13:59:56 GMT

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.