3 Replies Latest reply: Jan 18, 2013 3:31 AM by Nirav Bhimani RSS

    Combining periods of different Income statements

    Hennie Welman

      Hi, I've been struggling with something but am sure there's a simple way to fix it.

       

      I've got 3 different Income Statements (Actual, Budget, Projected) for multiple periods which I want to compare in one pivot table. I've linked the income and expenses from the different statements with an account number (Account) - see below. Becuase I want to compare it on one pivot table I want to be able to select a period only once and the figures must be updated. I've therefore given all periods the same field name but are getting a synthetic key becuase the statements are already linked to each other by Account. Is there a way that I can link the 3 different periods to each other without getting the synthetic key?

       

      //Budget Income Statement
      Directory;
      CrossTable(IS_Period, Budget_amount, 2)
      LOAD Account, 
           [Account name] as [Budget Account name], 
           [2012.1], 
           [2012.11], 
           [2012.12], 
           [2013.01], 
           [2013.02], 
           [2013.03], 
           [2013.04], 
           [2013.05], 
           [2013.06], 
           [2013.07], 
           [2013.08], 
           [2013.09], 
           [2013.1], 
           [2013.11], 
           [2013.12]
      FROM
      [...]
      (ooxml, embedded labels, table is [Budget_Income statement]);
      
      
      
      
      //PROJECTED INCOME STATEMENT
      Directory;
      CrossTable(IS_Period, Projected_amount, 2)
      LOAD Account, 
           [Account name] as [Projected Account name], 
           [2012.1], 
           [2012.11], 
           [2012.12], 
           [2013.01]
      FROM
      [...]
      (ooxml, embedded labels, header is 1 lines, table is Projected_IS);
      
      
      
      
      //ACTUAL TRIAL BALANCE
      Directory;
      CrossTable(IS_Period, Pastel_amount, 2)
      LOAD Account, 
           [Account name] as [Pastel Account name], 
           [2012.1], 
           [2012.11],
           [2012.12]
      FROM
      [...]
      (ooxml, embedded labels, table is [Actual TB]);
      
      
        • Re: Combining periods of different Income statements
          Nirav Bhimani

          Hi,

           

          You have to use link table for that, Can you share demo file so that I can help you further.

           

          Regards,

          Nirav Bhimani

            • Re: Combining periods of different Income statements
              Hennie Welman

              Hi Nariv

               

              I appreciate your help. See attached for a demo file.

                • Re: Combining periods of different Income statements
                  Nirav Bhimani

                  Hi,

                   

                  Try this Script,

                   

                  //Budget Income Statement

                   

                  Directory;

                  Tbl1:

                  CrossTable(IS_Period, Budget_amount)

                  LOAD Account,

                       [2012.1],

                       [2012.11],

                       [2012.12],

                       [2013.01],

                       [2013.02],

                       [2013.03],

                       [2013.04],

                       [2013.05],

                       [2013.06],

                       [2013.07],

                       [2013.08],

                       [2013.09],

                       [2013.1],

                       [2013.11],

                       [2013.12]

                  FROM

                  [Proposed Budget.xlsx]

                  (ooxml, embedded labels, table is [Budget_Income statement]);

                   

                  //PROJECTED INCOME STATEMENT

                  Directory;

                  Tbl2:

                  CrossTable(IS_Period, Projected_amount, 1)

                  LOAD Account,

                       [2012.1],

                       [2012.11],

                       [2012.12],

                       [2013.01]

                  FROM

                  [C:\Qlikview\Projected Inc_Statement.xlsx]

                  (ooxml, embedded labels, header is 1 lines, table is Projected_IS);

                   

                   

                  //ACUTAL TRIAL BALANCE

                  Directory;

                  Tbl3:

                  CrossTable(IS_Period, Actual_amount, 1)

                  LOAD Account,

                       [2012.1],

                       [2012.11],

                       [2012.12]

                  FROM

                  [C:\Qlikview\Actual TB.xlsx]

                  (ooxml, embedded labels, table is [Actual TB]);

                   

                  //New Tables with Composite Key...................................................................

                  NT1:

                  Load *, Account &'-'&IS_Period as Tbl1_Link

                  Resident Tbl1;

                   

                  NT2:

                  Load *, Account &'-'&IS_Period as Tbl2_Link

                  Resident Tbl2;

                   

                  NT3:

                  Load *, Account &'-'&IS_Period as Tbl3_Link

                  Resident Tbl3;

                   

                  //Link Table.....................................................................................................................

                  Link_Table:

                  Load

                             IS_Period as CalendarDate,

                             Account,

                             'Tbl1' as Source,

                             Account &'-'&IS_Period as Tbl1_Link

                  Resident NT1;

                   

                  Concatenate

                   

                  Load

                       IS_Period as CalendarDate,

                             Account,

                             'Tbl2' as Source,

                             Account &'-'&IS_Period as Tbl2_Link

                  Resident NT2;

                   

                  Concatenate

                   

                  Load

                       IS_Period as CalendarDate,

                             Account,

                             'Tbl3' as Source,

                             Account &'-'&IS_Period as Tbl3_Link

                  Resident NT3;

                   

                   

                  Drop Fields IS_Period, Account from NT1;

                  Drop Fields IS_Period, Account from NT2;

                  Drop Fields IS_Period, Account from NT3;

                   

                  Drop Table Tbl1;

                  Drop Table Tbl2;

                  Drop Table Tbl3;

                   

                  //Master Calendar..............................................................................................

                  Calendar: 

                  LOAD 

                  Date(CalendarDate,'MM/DD/YYYY') AS CalendarDate,

                  Day(CalendarDate) AS CalendarDayOfMonth, 

                  WeekDay(CalendarDate) AS CalendarDayName, 

                  Week(CalendarDate) AS CalendarWeek, 

                  Month(CalendarDate) AS CalendarMonth, 

                  'Q' & Ceil(Month(CalendarDate)/3) AS CalendarQuarter, 

                  Year(CalendarDate) AS CalendarYear

                  Resident Link_Table;

                   

                  //Hope this will resolved your problem.

                   

                  Regards,

                  Nirav Bhimani