Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
gidon500
Contributor II

Spead income by dates in month

Hi guys

I need help , enclosed is an excel file

the input is

invoice , amount , fromdate to date

I need to spead the amount by months , while take in consideration

number of days in a month , which calculated as 30 days per month

thanks for your help

gidon

1 Solution

Accepted Solutions

Re: Spead income by dates in month

I am sure there mught be a better way to do this, but I was able to go this far:

Table:

LOAD Invoice,

  Amount,

  FromDate,

  ToDate,

  Date(AddMonths(MonthEnd(FromDate), (IterNo() -1)), 'MMM-YYYY') as Date,

  If(IterNo() = 1, 30 - Day(FromDate),

  If(Floor(AddMonths(MonthEnd(FromDate), (IterNo() -1))) = Floor(MonthEnd(ToDate)),

  If(Match(Num(Month(ToDate)), 1, 3, 5, 7, 8, 10, 12) and Match(Day(ToDate), 31) or

  Match(Num(Month(ToDate)), 2) and Day(ToDate)>= 28, 30, Day(ToDate)), 30)) as Days

While AddMonths(MonthEnd(FromDate), (IterNo() -1)) <= MonthEnd(ToDate);

LOAD * Inline [

Invoice, Amount, FromDate, ToDate

100, 1500, 10/11/2015, 6/2/2016

101, 1000, 1/12/2015, 31/01/2016

102, 2700, 27/12/2015, 2/2/2016

];

Join(Table)

LOAD Invoice,

  Sum(Days) as Sum

Resident Table

Group By Invoice;

FinalTable:

LOAD Invoice,

  Date,

  (Amount * Days)/Sum as AmountPerMonth

Resident Table;

DROP Table Table;


Capture.PNG


The complication I faced was around getting 30 days for each month instead of the actual number of days. Somebody else might have a better solution

4 Replies

Re: Spead income by dates in month

I am sure there mught be a better way to do this, but I was able to go this far:

Table:

LOAD Invoice,

  Amount,

  FromDate,

  ToDate,

  Date(AddMonths(MonthEnd(FromDate), (IterNo() -1)), 'MMM-YYYY') as Date,

  If(IterNo() = 1, 30 - Day(FromDate),

  If(Floor(AddMonths(MonthEnd(FromDate), (IterNo() -1))) = Floor(MonthEnd(ToDate)),

  If(Match(Num(Month(ToDate)), 1, 3, 5, 7, 8, 10, 12) and Match(Day(ToDate), 31) or

  Match(Num(Month(ToDate)), 2) and Day(ToDate)>= 28, 30, Day(ToDate)), 30)) as Days

While AddMonths(MonthEnd(FromDate), (IterNo() -1)) <= MonthEnd(ToDate);

LOAD * Inline [

Invoice, Amount, FromDate, ToDate

100, 1500, 10/11/2015, 6/2/2016

101, 1000, 1/12/2015, 31/01/2016

102, 2700, 27/12/2015, 2/2/2016

];

Join(Table)

LOAD Invoice,

  Sum(Days) as Sum

Resident Table

Group By Invoice;

FinalTable:

LOAD Invoice,

  Date,

  (Amount * Days)/Sum as AmountPerMonth

Resident Table;

DROP Table Table;


Capture.PNG


The complication I faced was around getting 30 days for each month instead of the actual number of days. Somebody else might have a better solution

MVP
MVP

Re: Spead income by dates in month

1.png

V:

LOAD

  *,

  (daysfirstmonth/30 + monthdiff + daylastmonth/30) as period;

LOAD

  *,

  year(todate)*12 + month(todate) - (Year(fromdate)*12 + Month(fromdate))-1 as monthdiff,

  rangemin(30 - day(fromdate), 30)  as daysfirstmonth,

  rangemin(day(todate) - 1 +1,30)  as daylastmonth

  ;

LOAD

  //MonthStart(todate) as end,

  //Date(floor(MonthEnd(fromdate))+1) as start, 

  Invoice,

    amount,

    fromdate,

    todate

FROM

[Spead Income.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Len(Trim(Invoice))>0

;

Left Join (V)

load

  fromdate,

  date(AddMonths(MonthStart(fromdate), IterNo()-1), 'MMM-YY') as month

Resident V

While AddMonths(MonthStart(fromdate), IterNo()-1) <= MonthStart(todate);

Left Join (V) LOAD

  fromdate, month, 1 as IsFirst

Resident V

Where Peek('fromdate') <> fromdate

order by fromdate, month;

Left Join (V) LOAD  fromdate, month, 1 as IsLast

Resident V

Where Peek('fromdate') <> fromdate

order by fromdate, month desc;

gidon500
Contributor II

Re: Spead income by dates in month

Hello

thank you very much

gidon

gidon500
Contributor II

Re: Spead income by dates in month

Hi

thanks for your help

gidon

Community Browser