Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

agni_gold
Valued Contributor III

Distribute Month values to Date

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.

1 Solution

Accepted Solutions

Re: Distribute Month values to Date

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

];


Capture.PNG

View solution in original post

4 Replies

Re: Distribute Month values to Date

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

];


Capture.PNG

Highlighted
agni_gold
Valued Contributor III

Re: Distribute Month values to Date

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

Re: Distribute Month values to Date

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

];


Capture.PNG

View solution in original post

agni_gold
Valued Contributor III

Re: Distribute Month values to Date

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

Really thanks Sunny