Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Pick most recent value in list

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

KPI
  Description
KPIMonthJanFebMarAprMayJunJulAugSepOctNovDecYTD
Total Overhead Absorption % YTD103.9108.9111.5107.2102.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.

3 Replies
Highlighted
Not applicable

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)

Highlighted
Partner
Partner

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.

Highlighted
Not applicable

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.)