topic Re: Lookup corresponding field value in UI in QlikView

Lookup corresponding field value in UI

Wed, 25 Feb 2015 16:19:18 GMT

Hi all

I have a calendar table that contains each calendar date for a chosen period and it also includes lookup fields like Year, Month and a concatenation of Month and Year.

CalendarDate	CalendarYear	CalendarMonth	MonthYear	MonthYearSeq
01/02/2015	2015	February	Feb-2015	2
02/02/2015	2015	February	Feb-2015	2
01/03/2015	2015	March	Mar-2015	3
01/04/2015	2015	March	Apr-2015	4
01/02/2016	2016	February	Feb-2016	13

The MonthYearSeq starts at 1 for the earliest month in the calendar, and increments by 1 for each subsequent month. I use this field in variables and set analysis to find the previous month (MonthYearSeq-1) and the same month in the previous year (MonthYearSeq-12) to the MonthYear that the user has selected.

I would like to use the 'previous month' and 'same month in the previous year' in table headings by specifying the corresponding MonthYear to the MonthYearSeq derived by the variables.

Is there a lookup function or similar that can be used in the UI?

Thanks in advance.

Re: Lookup corresponding field value in UI

JonnyPoole — Wed, 25 Feb 2015 20:22:37 GMT

you can use the Only() function with Set Analysis to return a single value

Only( {$<MonthYearSeq=<correctsyntax>} MonthYear)

...where 'correctsyntax' will be an expression that evaluates to just one MonthYearSeq value for the whole table.

Re: Lookup corresponding field value in UI

maxgro — Wed, 25 Feb 2015 20:28:17 GMT

if I understand your question

I used for field heading (or if you want table heading)

=Only({$ <MonthYear=,MonthYearSeq={"$(=max(MonthYearSeq)-1)"}>} MonthYear)

Re: Lookup corresponding field value in UI

Thu, 26 Feb 2015 09:02:03 GMT

Thank you both, very useful.