Hi Nilesh,
Try the below, as I was not able to run your QVW , I did the below.
1) Created a new QVW with the below code
BINARY Inv POC.qvw;
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
drop field minperiod;
NewtABLE:
LOAD AsOfPeriod,
date(Period-359,'MM/DD/YYYY') as min_last_360,
date(Period-269,'MM/DD/YYYY') as min_last_270,
date(Period-179,'MM/DD/YYYY') as min_last_180,
date(Period-89,'MM/DD/YYYY') as min_last_90,
date(Period-59,'MM/DD/YYYY') as min_last_60,
date(Period-29,'MM/DD/YYYY') as min_last_30
Resident [CALENDAR_30_60_90]
where same_day = 'Y';
test:
Crosstable
(Category, Period,1) Load * Resident NewtABLE;
DROP table NewtABLE;
concatenate('CALENDAR_30_60_90')
load distinct * ,
'Turover' as source
resident test;
DROP table test;
2) Change the expression of Beg Mkt val as
= sum( aggr( DISTINCT
sum( {<Last = {'last_30'}, [Type of rec] = {'Inventory'},
Period= {'$(=date($(vMinPeriod)))'}
>} [Mkt Val]
)
, Period
)
)
As I was not able to do it on your file, may be it is not optimized script.
What you should do is remove the join Add this as new record set.
join([CALENDAR_30_60_90])
LOAD AsOfPeriod,
NewAsOfPeriod,
Period,
Last,
IF(Last = 'last_360',date(AsOfPeriod-359,'MM/DD/YYYY'),
IF(Last = 'last_270',date(AsOfPeriod-269,'MM/DD/YYYY'),
IF(Last = 'last_180',date(AsOfPeriod-179,'MM/DD/YYYY'),
IF(Last = 'last_90',date(AsOfPeriod-89,'MM/DD/YYYY'),
IF(Last = 'last_60',date(AsOfPeriod-59,'MM/DD/YYYY'),
IF(Last = 'last_30',date(AsOfPeriod-29,'MM/DD/YYYY'),NULL())
))))) as minperiod
Resident [CALENDAR_30_60_90];
