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

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

    Jeff Robertz

      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.

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

          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
            Marco Wedel

            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

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

                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
                  • Re: Distribute amount by month using total Amount, start and end date
                    Jeff Robertz

                    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
                      Marco Wedel

                      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