topic Re: Issue with the Beginning Value in QlikView

Issue with the Beginning Value

Anonymous — Tue, 11 Feb 2014 18:57:29 GMT

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 ..

Re: Issue with the Beginning Value

Tue, 11 Feb 2014 20:57:26 GMT

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];

Re: Issue with the Beginning Value

maxgro — Tue, 11 Feb 2014 21:44:14 GMT

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)