Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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.)