In your result table for USA, Nov01, it should be 10 instead of 8, right?
Please look at attached sample file. I used two approaches (you need only one of them), I think most simple is:
INPUT:
LOAD Country,
Date(Date#(CALMONTH,'MMM-YY'),'MMM-YY') as CALMONTH,
[Product Count]
FROM
[http://community.qlik.com/thread/39299]
(html, codepage is 1252, embedded labels, table is @3);
INPUT2:
LOAD Country as Country2, CALMONTH as CALMONTH_START, [Product Count] as [Product Count2],
if(peek(Country2)=Country, date(peek(CALMONTH_START)-1,'MMM-YY'), makedate(2002,02,28) ) as CALMONTH_END
resident INPUT order by Country, CALMONTH desc;
RESULT2:
LOAD
Date(addmonths(CALMONTH_START,iterno()-1),'MMM-YY') as CALMONTH2b
,Country2 as Country2b
,[Product Count2] as [Product Count2b]
resident INPUT2 WHILE addmonths(CALMONTH_START,iterno()-1) <= CALMONTH_END;
I think you could merge INPUT2 and INPUT loads, if you read in from your original data source.
The basic idea is to generate a end date for each record (just a little smaller than the next entry date for the next record of this country. Then use a while loop in the following load to create a record per country and product count from entry date to end date.
You could also use another approach using intervalmatch after you have created the two dates, but that seems to be a little more complex.
Hope this helps,
Stefan
