Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]);
Hi,
You have to use link table for that, Can you share demo file so that I can help you further.
Regards,
Nirav Bhimani
Hi Nariv
I appreciate your help. See attached for a demo file.
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
(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
(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