Discussion Board for collaboration related to QlikView App Development.
I have a table with a field for each month Jan-Dec, each month I upload the most recent data:
For YTD I need to pick the last non-zero, not-null value.
Currently I use :
'G3' as KPIID,
$(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.
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.
I think the alternative is a rather lengthy construction such as:
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.
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.)