Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Something like attached file?
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:
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
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 |
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;
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
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?