Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

2 Replies
whiteline
Master II
Master II

Hi.

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

Not applicable
Author

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.