topic Pick most recent value in list in QlikView

Pick most recent value in list

martynlloyd — Thu, 07 Aug 2014 11:34:23 GMT

I have a table with a field for each month Jan-Dec, each month I upload the most recent data:

KPI Description	KPIMonth	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	YTD
Total Overhead Absorption % YTD		103.9	108.9	111.5	107.2	102.3	-	-	-	-	-	-	-	102.3

For YTD I need to pick the last non-zero, not-null value.

Currently I use :

...

Concatenate

LOAD

'G3' as KPIID,

DealerID,

$(KPICurrentYear) as KPIYear,

Dual('YTD',13) as KPIMonth,

Pick(Month(Today())-1, Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec) as OHAbsRate

...

But sometimes the most recent is current month -2, so I am looking for a more robust method.

Regards,

Marty.

Re: Pick most recent value in list

Thu, 07 Aug 2014 15:01:13 GMT

Unless there's any reason you need cross tables in the in memory tables, I'd suggest converting the cross table to a straight table and then proceed from there.

See: http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable

I think the alternative is a rather lengthy construction such as:

alt(pick(month-1..),pick(month-2...),etc)

Re: Pick most recent value in list

martynlloyd — Thu, 07 Aug 2014 15:09:05 GMT

Japser, that is what I am doing, reading in a cross table and creating a straight table (note the 'Concatenate' in my script). the problem comes when I need to add month 13, which is not in the source input.

Re: Re: Pick most recent value in list

Thu, 07 Aug 2014 15:25:24 GMT

See attachment for what I had in mind.

Then use the regular QV methods to load the latest month's value. (set expressions, group by, etc.)