7 Replies Latest reply: Aug 12, 2015 8:19 PM by Jeff Robertz

# 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:

 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

Something like attached file?

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

Try the following script:

Table:

Amount/NoOfMonths as EachMonthAmount;

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

[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:

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)

MonthEnd

Resident Table;

DataModel:

Output in a table box object:

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

Hi,

another solution could be:

```tabAmount:
Money(Amount/((Month([Alloc End])-Month([Alloc Start]))+12*(Year([Alloc End])-Year([Alloc Start]))+1)) as Amount,
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))))

tabMonths:
Resident tabAmount;

```

hope this helps

regards

Marco

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

Hi Marco,

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

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:

[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,

FROM

[Step 1 - INPUT DPT MANAGER*.xlsx]

(ooxml, embedded labels, table is Technical)

DROP Table Technical;

tabMonths:

Resident tabAmount;

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

Hi,

```tabAmount:
[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],
FROM [https://community.qlik.com/thread/175834] (html, codepage is 1252, embedded labels, table is @3)

tabMonths:
Resident tabAmount;
```

hope this helps

regards

Marco

• ###### 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?