Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to create a turnover Ratio. The turnover ratio will have numerator as sum of transactions over specific days and then divide it by beginning value of the corresponding period.
The data will be like
Tran day 1 10
Tran day 2 10
Tran day 3 10
Tran day 4 10
Tran day . 10
Tran day 30 10
Inve day 1 200
The ratio should give me (300/200) = 1.5
This ratio should be displayed as running ratio for last 30/60/90 days. So we have created one 30-60-90 days calendar which provides corresponding 30-60-90 days for given period.
Im not able to get the beginning of Inv value consistently. It comes only for the first record, that too after the selection is made.
Any idea why the values are not displayed for other rows ..
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];
try to add this expression to your "Chart with a question"
subfield(concat(total aggr(Sum({$ <[Type of rec] = {'Inventory'}>} [Mkt Val]), Period ), '-', Period), '-', 1)