Spread in come ( by Days)

[gidon500]

Hi guys

i need help  , i got this table of income :

invoice	fromdate	todate	sum
150	15/01/2015	22/03/2015	300
151	04/02/2015	17/06/2015	600
152	17/03/2015	13/07/2015	1200

as you can see invoice 150 is from jan 15 to march 22

the total income  for this period is 300

i need to spread the income for jan as 15 days , feb total month , and march 22 days

so each period get a portion from the 300

Invoices 151 and 152  are two more examples .

I got tousands of them during a year

thanks for the help .

gidon

[sunny_talwar]

Try the following script:

Table:

LOAD *,

  sum/Diff as EachDaySum;

LOAD *,

  todate - fromdate + 1 as Diff;

LOAD * Inline [

invoice, fromdate, todate, sum

150, 15/01/2015, 22/03/2015, 300

151, 04/02/2015, 17/06/2015, 600

152, 17/03/2015, 13/07/2015, 1200

];

Temp:

LOAD Min(fromdate) as minDate,

  Max(todate) as maxDate

Resident Table;

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD Date($(varMinDate) + IterNo() - 1) as Date 

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Join (Table)

IntervalMatch (Date)

LOAD fromdate,

  todate

Resident Table;

[Kushal_Chawda · Accepted solution]

something like this

Table:

LOAD * Inline [

invoice,    fromdate,    todate,        sum

150,        15/01/2015,    22/03/2015,    300

151,        04/02/2015,    17/06/2015,    600

152,        17/03/2015,    13/07/2015,    1200

];

Data:

LOAD *,

    date(monthstart(fromdate)+(iterNo()-1),'DD-MM-YYYY') as Date,

    monthname(monthstart(fromdate)+(iterNo()-1)) as MonthName,

    sum/(todate - monthstart(fromdate)+1) as DaySum

RESIDENT Table

WHILE iterNo() <= (todate - monthstart(fromdate)) + 1;

DROP Table Table;

[gidon500]

Hello

thanks  it works great

gidon

[gidon500]

Hello

thanks it works

gidon

[Kushal_Chawda]

I am glad that I was able to help

[sunny_talwar]

Awesome

Glad it did.

Best,

Sunny

[gidon500]

hi

i found a problem

if fromdate is 15/01/2015

it consider like date is 01/01/2015

can you look at it

thanks

gidon

[Kushal_Chawda]

Hi ,

That is because, I have used monthstart function over fromdate.

try below

Table:

LOAD * Inline [

invoice, fromdate, todate, sum

150, 15/01/2015, 22/03/2015, 300

151, 04/02/2015, 17/06/2015, 600

152, 17/03/2015, 13/07/2015, 1200

];

Data:

LOAD *,

  date(fromdate+(iterNo()-1),'DD-MM-YYYY') as Date,

  monthname(fromdate+(iterNo()-1)) as MonthName,

  sum/(todate - fromdate+1) as DaySum

RESIDENT Table

WHILE iterNo() <= todate -fromdate + 1;

DROP Table Table;

[gidon500]

hi

thanks , now it works perfect

gidon