Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

agaetisproject
New Contributor III

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

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:

 

AmountAlloc StartAlloc End
$1,000.001/01/201431/03/2014
$600.001/01/201430/06/2014
$900.001/01/201431/12/2014
-$750.001/01/201431/03/2014
$800.001/01/201430/04/2014
$650.001/03/201430/06/2014
$850.001/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/20141/02/20141/03/20141/04/20141/05/20141/06/20141/07/20141/08/20141/09/20141/10/20141/11/20141/12/20141/01/20151/02/2015
333.33333.33333.330.000.000.000.000.000.000.000.000.000.000.00
100.00100.00100.00100.00100.00100.000.000.000.000.000.000.000.000.00
75.0075.0075.0075.0075.0075.0075.0075.0075.0075.0075.0075.000.000.00
-250.00-250.00-250.000.000.000.000.000.000.000.000.000.000.000.00
200.00200.00200.00200.000.000.000.000.000.000.000.000.000.000.00
0.000.00162.50162.50162.50162.500.000.000.000.000.000.000.000.00
0.000.000.000.000.00121.43121.43121.43121.43121.43121.43121.430.000.00

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

Thanks a lot for your help!

The file is attached.

7 Replies
maximiliano_vel
Contributor III

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

Something like attached file?

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

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:

Capture.PNG

Output in a table box object:

Capture.PNG

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

Hi,

another solution could be:

QlikCommunity_Thread_175834_Pic1.JPG

QlikCommunity_Thread_175834_Pic2.JPG

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

agaetisproject
New Contributor III

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

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. ExpenditureTitleCatActivityWPStart DateEnd DateDaysMonthNumber/amount
Tender_nameDepartment-Version3. Travel (cat. PSS form 3.1)Jeff test00WP1.11/01/201731/12/2017126000
Tender_nameDepartment-Version3. Travel (cat. PSS form 3.1)Jeff test00WP1.11/06/201731/12/2017128008
Tender_nameDepartment-Version3. Travel (cat. PSS form 3.1)Jeff test00WP1.11/01/201731/05/20171210020
Tender_nameDepartment-Version3. Travel (cat. PSS form 3.1)Jeff test00WP1.11/04/201731/12/20171212036
agaetisproject
New Contributor III

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

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

Hi,

using your sample data:

QlikCommunity_Thread_175834_Pic3.JPG

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

agaetisproject
New Contributor III

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

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?

Community Browser