topic Spread in come ( by Days) in QlikView

Spread in come ( by Days)

gidon500 — Sun, 23 Aug 2015 06:45:16 GMT

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

Re: Spread in come ( by Days)

sunny_talwar — Sun, 23 Aug 2015 07:52:25 GMT

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;

Re: Spread in come ( by Days)

Kushal_Chawda — Sun, 23 Aug 2015 09:25:35 GMT

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;

Re: Spread in come ( by Days)

gidon500 — Sun, 23 Aug 2015 10:52:44 GMT

Hello

thanks it works great

gidon

Re: Spread in come ( by Days)

gidon500 — Sun, 23 Aug 2015 10:53:08 GMT

Hello

thanks it works

gidon

Re: Spread in come ( by Days)

Kushal_Chawda — Sun, 23 Aug 2015 10:57:19 GMT

I am glad that I was able to help

Re: Spread in come ( by Days)

sunny_talwar — Sun, 23 Aug 2015 13:14:19 GMT

Awesome

Glad it did.

Best,

Sunny

Re: Spread in come ( by Days)

gidon500 — Mon, 24 Aug 2015 08:15:57 GMT

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

Re: Spread in come ( by Days)

Kushal_Chawda — Mon, 24 Aug 2015 09:32:08 GMT

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;

Re: Spread in come ( by Days)

gidon500 — Mon, 24 Aug 2015 18:21:49 GMT

thanks , now it works perfect

gidon