topic Distribute amount by month using total Amount, start and end date in QlikView

Distribute amount by month using total Amount, start and end date

agaetisproject — Fri, 07 Aug 2015 21:43:32 GMT

Hi all,

I need to distribute amount given as input on a monthly basis between a start and an end date.

The input file is very simple and looks like this:

Amount	Alloc Start	Alloc End
$1,000.00	1/01/2014	31/03/2014
$600.00	1/01/2014	30/06/2014
$900.00	1/01/2014	31/12/2014
-$750.00	1/01/2014	31/03/2014
$800.00	1/01/2014	30/04/2014
$650.00	1/03/2014	30/06/2014
$850.00	1/06/2014	31/12/2014

I need to do the following:

1. calculate the minimum date and

2. distribute the amount along the dates.

Using the inputs above,it would give this:

1/01/2014	1/02/2014	1/03/2014	1/04/2014	1/05/2014	1/06/2014	1/07/2014	1/08/2014	1/09/2014	1/10/2014	1/11/2014	1/12/2014	1/01/2015	1/02/2015
333.33	333.33	333.33	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
100.00	100.00	100.00	100.00	100.00	100.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
75.00	75.00	75.00	75.00	75.00	75.00	75.00	75.00	75.00	75.00	75.00	75.00	0.00	0.00
-250.00	-250.00	-250.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
200.00	200.00	200.00	200.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
0.00	0.00	162.50	162.50	162.50	162.50	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
0.00	0.00	0.00	0.00	0.00	121.43	121.43	121.43	121.43	121.43	121.43	121.43	0.00	0.00

Is there any easy way to do that in QlikView script?

Thanks a lot for your help!

The file is attached.

Re: Distribute amount by month using total Amount, start and end date

maximiliano_vel — Sat, 08 Aug 2015 00:41:51 GMT

Something like attached file?

Re: Distribute amount by month using total Amount, start and end date

sunny_talwar — Sat, 08 Aug 2015 03:33:12 GMT

Try the following script:

Table:

LOAD *,

Amount/NoOfMonths as EachMonthAmount;

LOAD *,

Round((Num(MonthEnd) - Num(MonthStart))/30) + 1 as NoOfMonths;

LOAD Amount,

[Alloc Start],

MonthName([Alloc Start]) as MonthStart,

[Alloc End],

MonthName([Alloc End]) as MonthEnd

FROM

[Monthly allocation.xlsx]

(ooxml, embedded labels, table is Sheet2);

Temp:

Load Min([Alloc Start]) as minDate,

Max([Alloc End]) as maxDate

Resident Table;

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

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

DROP Table Temp;

Calendar:

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

MonthName(Date($(varMinDate) + IterNo() - 1)) as MonthYear

AutoGenerate 1

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

IntervalMatch:

IntervalMatch(MonthYear)

LOAD MonthStart,

MonthEnd

Resident Table;

DataModel:

Output in a table box object:

Re: Distribute amount by month using total Amount, start and end date

MarcoWedel — Sun, 09 Aug 2015 00:01:00 GMT

Hi,

another solution could be:

tabAmount:
LOAD RecNo() as ID,
    Money(Amount/((Month([Alloc End])-Month([Alloc Start]))+12*(Year([Alloc End])-Year([Alloc Start]))+1)) as Amount,
    AddMonths([Alloc Start],IterNo()-1) as Month
FROM [https://community.qlik.com/servlet/JiveServlet/download/840450-179585/Monthly%20allocation.xlsx] (ooxml, embedded labels, table is Sheet1, filters(Remove(Row, Pos(Top, 5)),Remove(Row, Pos(Top, 4)),Remove(Row, Pos(Top, 3)),Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 1))))
While AddMonths([Alloc Start],IterNo()-1)<[Alloc End];

tabMonths: 
LOAD AddMonths(MinMonth,IterNo()-1) as Month
While AddMonths(MinMonth,IterNo()-1)<=Today();
LOAD Min(Month) as MinMonth
Resident tabAmount;

hope this helps

regards

Marco

Re: Distribute amount by month using total Amount, start and end date

agaetisproject — Wed, 12 Aug 2015 21:39:40 GMT

Hi Marco,

Many thanks for your reply.

While this seems pretty simple, I can't manage to replicate the principle on my own file (I am not a script guru but am learning thanks to this great community).

I attach here a sample of my real file, would you mind instructing me how I can transpose your script above on this file structure. That would really help me!

PROJECT NAME :	DEPARTMENT :	Cat. Expenditure	Title	Cat	Activity	WP	Start Date	End Date	Days	Month	Number/amount
Tender_name	Department-Version	3. Travel (cat. PSS form 3.1)	Jeff test	0	0	WP1.1	1/01/2017	31/12/2017	12	6000
Tender_name	Department-Version	3. Travel (cat. PSS form 3.1)	Jeff test	0	0	WP1.1	1/06/2017	31/12/2017	12	8008
Tender_name	Department-Version	3. Travel (cat. PSS form 3.1)	Jeff test	0	0	WP1.1	1/01/2017	31/05/2017	12	10020
Tender_name	Department-Version	3. Travel (cat. PSS form 3.1)	Jeff test	0	0	WP1.1	1/04/2017	31/12/2017	12	12036

Re: Distribute amount by month using total Amount, start and end date

agaetisproject — Wed, 12 Aug 2015 21:43:12 GMT

I also attach my script.

As you will notice, I am loading several input files within a same folder and the idea is to calculate the distribution on all files:

tabAmount:

LOAD

[PROJECT NAME :],

[DEPARTMENT :],

[Cat. Expenditure],

Title,

Cat,

Activity,

WP,

[Start Date],

MonthName([Start Date]) as [Alloc Start],

[End Date],

Monthname([End Date]) as [Alloc End],

Days,

Month,

[Number/amount],

RecNo() as ID,

Money(Amount/((Month([Alloc End])-Month([Alloc Start]))+12*(Year([Alloc End])-Year([Alloc Start]))+1)) as Amount,

AddMonths([Alloc Start],IterNo()-1) as Month

FROM

[Step 1 - INPUT DPT MANAGER*.xlsx]

(ooxml, embedded labels, table is Technical)

While AddMonths([Alloc Start],IterNo()-1)<[Alloc End];;

DROP Table Technical;

tabMonths:

LOAD AddMonths(MinMonth,IterNo()-1) as Month

While AddMonths(MinMonth,IterNo()-1)<=Today();

LOAD Min(Month) as MinMonth

Resident tabAmount;

Re: Distribute amount by month using total Amount, start and end date

MarcoWedel — Wed, 12 Aug 2015 23:04:48 GMT

Hi,

using your sample data:

tabAmount:
LOAD RecNo() as ID,
    [PROJECT NAME :],
    [DEPARTMENT :],
    [Cat. Expenditure],
    Title,
    Cat,
    Activity,
    WP,
    [Start Date],
    [End Date],
    Money(Month/((Month([End Date])-Month([Start Date]))+12*(Year([End Date])-Year([Start Date]))+1)) as [Number/amount],
    AddMonths([Start Date],IterNo()-1) as Month
FROM [https://community.qlik.com/thread/175834] (html, codepage is 1252, embedded labels, table is @3)
While AddMonths([Start Date],IterNo()-1)<[End Date];

tabMonths:   
LOAD AddMonths(MinMonth,IterNo()-1) as Month 
While AddMonths(MinMonth,IterNo()-1)<=Today(); 
LOAD Min(Month) as MinMonth
Resident tabAmount;

hope this helps

regards

Marco

Re: Distribute amount by month using total Amount, start and end date

agaetisproject — Thu, 13 Aug 2015 00:19:25 GMT

Thank you Marco, it really helps. I notice an edge case.

Indeed, I have a record that starts on 01/09/2015 and ends on 01/03/2016.

The result divides the amount by 7 (while my reference Excel Excel divides this by 6).

Is this because 2016 is a bisextile year? Is this taken into account in QlikView?

A possible workaround is to compute the number of month between two dates but there, I find a hard time doing it. This post supposedly helps but messes up the script: Calculating Months difference between two dates

There seems to be a parenthesis missing somewhere in the expression: ($(MonthDiff(StartDate, EndDate)) AS MonthsDifference

Thoughts?