topic Distribute Month values to Date in QlikView

Distribute Month values to Date

agni_gold — Mon, 13 Jun 2016 19:32:06 GMT

Hi friends,

suppose i have

2016 Jan 200 USD

2016 Feb 500 USD

I want to distribute month values to all dates and amount should divided by 31 for jan and 29 days for feb and all dates should come.

Re: Distribute Month values to Date

sunny_talwar — Mon, 13 Jun 2016 19:39:22 GMT

May be like this:

Table:

LOAD Date(Date + IterNo() - 1) as Date,

CURRENCY,

AMOUNT_PER_DAY

While Date + IterNo() - 1 <= EndDate;

LOAD *,

Floor(MonthEnd(Date)) as EndDate,

AMOUNT/(Floor(MonthEnd(Date)) - MonthStart(Date)) as AMOUNT_PER_DAY;

LOAD *,

Date(MonthStart(Date#(MONTH & '-' & YEAR, 'MMM-YYYY'))) as Date;

LOAD * INLINE [

YEAR, MONTH, AMOUNT, CURRENCY

2016, Jan, 200, USD

2016, Feb, 500, USD

];

Re: Distribute Month values to Date

agni_gold — Mon, 13 Jun 2016 21:01:49 GMT

Thanks but my main problem is feb here it is making total 517 not 500

Re: Distribute Month values to Date

sunny_talwar — Mon, 13 Jun 2016 21:04:31 GMT

How about now:

Table:

LOAD Date(Date + IterNo() - 1) as Date,

CURRENCY,

AMOUNT_PER_DAY

While Date + IterNo() - 1 <= EndDate;

LOAD *,

Floor(MonthEnd(Date)) as EndDate,

AMOUNT/(Floor(MonthEnd(Date)) - MonthStart(Date) + 1) as AMOUNT_PER_DAY;

LOAD *,

Date(MonthStart(Date#(MONTH & '-' & YEAR, 'MMM-YYYY'))) as Date;

LOAD * INLINE [

YEAR, MONTH, AMOUNT, CURRENCY

2016, Jan, 200, USD

2016, Feb, 500, USD

];

Re: Distribute Month values to Date

agni_gold — Mon, 13 Jun 2016 21:17:18 GMT

I need this code so urgent so that i am asking these type of question

Really thanks Sunny